Dax Measures

[peteru9067]

I am trying to write a dax measures for finding the number of dates between two dates in column A & B....... I do not want to add a calculated column but a dax measures that will perform the difference. I am using DATEDIFF but I cannot select the columns where the dates are I am assuming I need to use an aggregator or something, like calculate, max, min etc.

Column A = 06/20/2021

Column B = 05/24/2021

[Samarth_18 · Accepted solution]

You can try below code.

Date_Diff =

var calculate_diff = DATEDIFF(MAX('Dates_table'[Column A]),MAX('Dates_table'[Column B]),DAY)

return IF(calculate_diff < 0,calculate_diff * -1,calculate_diff)

[Samarth_18 · Accepted solution]

You can try below code.

Date_Diff =

var calculate_diff = DATEDIFF(MAX('Dates_table'[Column A]),MAX('Dates_table'[Column B]),DAY)

return IF(calculate_diff < 0,calculate_diff * -1,calculate_diff)

[peteru9067]

An extension to what I am actually trying to accomplish is calculating overdue dates for Compliance Testing. I can use calculated columns to get it done but I know there is probably a better way to use measures.

So with both columns Column A is Planned Date and Column B is Completed Date...... in my fact table if the test is yet to be performed column B is 12/31/1899. So if Planned date was 07/01/2021 and completed is 12/31/1899 that means the test is "overdue" by 11 days if counting today.

So here is what I think the code should be:

IF Column A <= today() && Column B = 12/31/1899, **some planned dates are in the future

Datediff = Column A, Column B

But what will the DAX Measure look like

[Samarth_18 · Accepted solution]

You can create measure with below code to achive this

Date_Differences =

VAR calculate_diff =

DATEDIFF ( MAX ( 'Test'[Column A] ), TODAY(), DAY )

RETURN

IF (

MAX ( 'Test'[Column A] ) <= TODAY ()

&& MAX ( 'Test'[Column B] ) = DATE ( 1899, 12, 31 ),

calculate_diff,

FORMAT ( MAX ( 'Test'[Column B] ), "MM/DD/YYYY" )

)

PFA screenshot of output

[peteru9067]

Thank you very much........ that worked perfectly well for me.