CODEWITHSUNDEEP
powerbidax
Shahab Haidar
Shahab Haidar

Reputation: 641

How to calculate date difference in a measure in power bi?

i am trying to use measure to calculate the number of days between two date and the further use of calculation,

when i am typing my column name in DATEDIFF function its not showing in dax measure.

but the moment i'm typing the number of days manually it works.

ClosingDays = 
30 * [Total Closing] / [Total Sales]

Please let me know how to get number of days in a month in measure.

Upvotes: 0

Views: 32696

Answers (1)

Jon
Jon

Reputation: 4967

You will need to use an iterator function to evaluate the row context, like the X series of DAX commands, SUMX,MINX, MAXX etc.

Assuming you have two columns, Start Date and End Date, you'll need a measure along the lines of:

No of Days = SUMX('Table', DATEDIFF('Table'[StartDate], 'Table'[EndDate], DAY))

No of Days in a measure

You can of course, add a calculated column using DATEDIFF if your data structure supports it, and then create a measure on that references that column.

Upvotes: 6

Related Questions