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 counts. Please suggest where does the below measure go wrong.
Expected output below:
| Test Frequency | Total Overdue count | ID | Last Test Date |
| Annual | ? | 22 | |
| Biennial | ? | 5558 | |
| Quarterly | ? | 543252 | |
| Every 18 Months | ? | 45245 | |
Total Overdue count =
var startdate = MAXX(Test,(Test[Last Test Date]))
var enddate = TODAY()
RETURN
CALCULATE (
IF (
MAX( Test[Test Frequency] ) = "Annually"
|| DATEDIFF ( startdate , enddate, MONTH ) > -12,
DISTINCTCOUNT ( Test[ID] ),
IF (
MAX( Test[Test Frequency] ) = "Biennial"
|| DATEDIFF ( startdate , enddate, MONTH ) > -24,
DISTINCTCOUNT ( Test[ID] ),
IF (
MAX( Test[Test Frequency] ) = "Six Monthly"
|| DATEDIFF ( startdate , enddate, MONTH ) > -6,
DISTINCTCOUNT ( Test[ID] ),
IF (
MAX( Test[Test Frequency] ) = "Quarterly"
|| DATEDIFF ( startdate , enddate, MONTH) > -3,
DISTINCTCOUNT (Test[ID] ),
IF (
MAX( Test[Test Frequency] ) = "Every 18 Months"
|| DATEDIFF ( startdate , enddate, MONTH ) > -18,
DISTINCTCOUNT ( Test[ID] ),
0
)
)
)
)
),
FILTER ( Test, Test[Status Name] = "Active" )
)
