Forum Discussion
Helper IIIDax Measures
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
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)You can create measure with below code to achive this
Date_Differences =VAR calculate_diff =DATEDIFF ( MAX ( 'Test'[Column A] ), TODAY(), DAY )RETURNIF (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
4 Replies
Samarth_18Community Champion
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)
peteru9067Thank you very much........ that worked perfectly well for me.
- 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
You can create measure with below code to achive this
Date_Differences =VAR calculate_diff =DATEDIFF ( MAX ( 'Test'[Column A] ), TODAY(), DAY )RETURNIF (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