Forum Discussion
Solved
Datediff with multiple criteria
Hello Everyone, I have a scenario where I want to calculate counts where dates and one of the column called Test Frequency is "Annually", "Biannieal"etc Below is my dax which gives me incorrect ...
Hi reddevil ,
Based on the information, try using the following DAX formula.
Total Overdue Count update = VAR StartDate = MAX(Test[Last Test Date]) VAR EndDate = TODAY() VAR Frequency = MAX(Test[Test Frequency]) VAR MonthDiff = DATEDIFF(StartDate, EndDate, MONTH) VAR OverdueThreshold = SWITCH( TRUE(), Frequency = "Annually", 12, Frequency = "Biennial", 24, Frequency = "Six Monthly", 6, Frequency = "Quarterly", 3, Frequency = "Every 18 Months", 18, BLANK() ) RETURN IF( NOT(ISBLANK( OverdueThreshold)) && MonthDiff >= OverdueThreshold, DISTINCTCOUNT(Test[ID]), 0 )Also, the example data you provided does not have the Test[ID] column and Test[Status Name] column in it.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi reddevil ,
Based on the information, try using the following DAX formula.
Total Overdue Count update =
VAR StartDate = MAX(Test[Last Test Date])
VAR EndDate = TODAY()
VAR Frequency = MAX(Test[Test Frequency])
VAR MonthDiff = DATEDIFF(StartDate, EndDate, MONTH)
VAR OverdueThreshold =
SWITCH(
TRUE(),
Frequency = "Annually", 12,
Frequency = "Biennial", 24,
Frequency = "Six Monthly", 6,
Frequency = "Quarterly", 3,
Frequency = "Every 18 Months", 18,
BLANK()
)
RETURN
IF(
NOT(ISBLANK( OverdueThreshold)) &&
MonthDiff >= OverdueThreshold,
DISTINCTCOUNT(Test[ID]),
0
)Also, the example data you provided does not have the Test[ID] column and Test[Status Name] column in it.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, this works.