Use 'Datediff' Function in a 'Custom Column'
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 and Do =...
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 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.
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])
It looks like you are in PowerQuery.
Duration.Days(Date.From(DateTime.LocalNow())-Date.From([Date Column]))
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
