topic Re: DATEDIFF as DAX Measure in DAX Commands and Tips

DATEDIFF as DAX Measure

Anonymous — Tue, 21 Jul 2020 12:21:23 GMT

Hello all,

I am trying to create a measure that calculates the difference between 2 time columns (hh:mm:ss format) and gives a result as a decimal. DATEDIFF works as a calculated column, but I cannot figure out how to translate to a DAX Measure. My report is saying there is too much space being taken up, so I want to change these columns to measures.

Please help!

Re: DATEDIFF as DAX Measure

amitchandak — Tue, 21 Jul 2020 12:30:42 GMT

Anonymous ,

[Date1] -[Date2] will give in that format.

If you get in in second, minute or hour refer this for the duration of your choice

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

Re: DATEDIFF as DAX Measure

Anonymous — Tue, 21 Jul 2020 12:40:56 GMT

The DAX measure does not give me the option to call the entire column, so I cannot use [Date 1] - [Date 2]. In this case I would need a function that allows me to subtract 2 columns that are in the date format.

Re: DATEDIFF as DAX Measure

amitchandak — Tue, 21 Jul 2020 12:46:31 GMT

Anonymous , in Measure you need to

measure = max([Date1]) -min([Date2])

Take care row context while using. refer this blog how summarize and valuea has been used

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

Re: DATEDIFF as DAX Measure

Anonymous — Tue, 21 Jul 2020 14:44:15 GMT

I tried that measure and when I put it in a table it only gives one value. I am trying to replicate the following column as a DAX measure:

This was calculated using the following calculation:

Start Time Variance =

DATEDIFF('bv AFKO: Order Header Data PP Orders'[GSUZS_SIMP_TM: (GC) Scheduled Start (Time)] , 'bv AFKO: Order Header Data PP Orders'[GSUZI_SIMP_TM: (GC) Actual start time] , MINUTE) / 60

The columns in the calculation are the following:

Re: DATEDIFF as DAX Measure

Anonymous — Wed, 22 Jul 2020 16:18:05 GMT

Hello, hoping you can read my last reply and provide some assistance please. I have a hard deadline for this project and I'm really trying to deliver it. Thank you!

Re: DATEDIFF as DAX Measure

mahoneypat — Thu, 23 Jul 2020 00:41:18 GMT

You could try either of these measure expressions

TimeDiff = SUMX(Table, Table[TimeColumn1] - Table[TimeColumn2]

TimeDiff = SUMX(Table, DATEDIFF(Table[TimeColumn1], Table[TimeColumn2], MINUTE))

If you make a table visual with both time columns, you should see the difference when you add the measure. Or you could use the aggregate, maybe switching to AVERAGEX, MAXX, etc. depending on what you are looking for.

Also, there are probably more impactful ways to reduce the file size of your model.

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: DATEDIFF as DAX Measure

Anonymous — Thu, 23 Jul 2020 13:14:44 GMT

Second measure worked using MAXX. Thank you very much!!!

Additionally, could you tell me what are the best ways to substantially reduce file size?