Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATEDIFF as DAX Measure

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 ca...
DATEDIFF
DAX
Help with DAX
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    You could try either of these measure expressions

     

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

     

    or


    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

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

You could try either of these measure expressions

 

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

 

or


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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

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