Use 'DateDiff' function in a 'Custom Column' -

Nov 27, 2023 Chauncey Koziol I am trying to create a custom column in PowerBI which calculates the number of days between Date column and today. So I follow the example from https://www.enjoysharepoint.com/power-bi-date-difference/#:~:text=To%20find%20the%20difference%20between%20the%20date%20from,measure%20%3ADiff%20%3D%20DATEDIFF%20%28Table1%20%5BDate%5D%2C%20TODAY%20%28%29%2CDAY%29 and do

Nov 27, 2023 Chauncey Koziol

I am trying to create a custom column in PowerBI which calculates the number of days between Date column and today. So I follow the example from https://www.enjoysharepoint.com/power-bi-date-difference/#:~:text=To%20find%20the%20difference%20between%20the%20date%20from,measure%20%3ADiff%20%3D%20DATEDIFF%20%28Table1%20%5BDate%5D%2C%20TODAY%20%28%29%2CDAY%29 and do

= Table.AddColumn(#"Grouped Rows", "Custom", each DateDiff([Date], Today(), Day))

But I get error saying Expression.Error: The name 'DateDiff' wasn't recognized. Make sure it's spelled correctly

My guess is I can't use DAX function in the 'Custom column formula` . But when I look at the available formula from the popup. I can't find one which calculates the number of days between 2 columns. enter image description here

2 Answers

No, you can't use DAX. Power Query uses M formula language. There you can use Duration to calculate a duration, and more specific Duration.Days in your case. Get the current date and time using for example DateTimeZone.UtcNow (working with UTC is recommended to avoid some unexpected results when publishing your report to the service for example) and convert that to a date using DateTime.Date. So the code for the custom column could be something like this:

Duration.Days(DateTime.Date(DateTimeZone.UtcNow()) - [Date]) 

enter image description here

It looks like you are in PowerQuery.

Duration.Days(Date.From(DateTime.LocalNow())-Date.From([Date Column])) 
2

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobm9qaWiEeYKOrqqeZZSWwaawyJ%2BdZp6lo7C1tc6nZKKmXZZ6pMHSraamZZOkuba5zQ%3D%3D

ncG1vNJzZmiipZ%2B1b7%2FFqGlnnJmctrWty6ianpmeqL2ir8SsZZynnWTCtLGMnZitnZSes6d5xa6lnKyZpLtutc1mmGabpajBsLmMnKalrZ2je6nAzKU%3D

 Share!