Forum Discussion
Helper IIDatediff 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 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" )
)
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.
7 Replies
- rajendraongole1
Super User
Hi reddevil - Please find the attached pbix file. i have modified the above formulae with correct datediff condition and max functions which is givng wrong results. check and confirm.
reddevilThanks rajendraongole1 Rajendra for responding, the below is the measure which doesnt display it correctly when I add the Date field.
Test Frequency Total Overdue count Last Test Date Next Test Date No of months old Six Monthly 1 16/12/2022 0:00 16/05/2023 0:00 26 Six Monthly 4 21/08/2024 0:00 31/07/2025 0:00 6 Six Monthly 0 4/09/2024 0:00 12/03/2025 0:00 5 Six Monthly 0 20/11/2024 0:00 20/05/2025 0:00 3 Quarterly 4 4/06/2024 0:00 6/06/2025 0:00 8 Quarterly 0 19/12/2024 0:00 14/04/2025 0:00 2 Every 18 Months 0 27/02/2024 0:00 28/08/2025 0:00 12 Biennial 1/08/2021 0:00 1/08/2023 0:00 42 Biennial 30/09/2021 0:00 30/09/2023 0:00 41 Biennial 29/10/2021 0:00 31/10/2023 0:00 40 Biennial 31/10/2021 0:00 31/10/2023 0:00 40 Biennial 29/11/2021 0:00 39 Biennial 1/12/2021 0:00 1/12/2023 0:00 38 Biennial 1/01/2022 0:00 1/01/2024 0:00 37 Biennial 28/02/2022 0:00 31/01/2024 0:00 36 Biennial 28/02/2022 0:00 28/02/2024 0:00 36 Biennial 2 3/03/2022 0:00 28/02/2026 0:00 35 Biennial 7/03/2022 0:00 28/02/2024 0:00 35 Biennial 10/03/2022 0:00 28/03/2024 0:00 35 Biennial 30/03/2022 0:00 28/03/2024 0:00 35 Biennial 31/03/2022 0:00 31/03/2024 0:00 35 Biennial 31/03/2022 0:00 1/04/2024 0:00 35 Biennial 2 31/03/2022 0:00 31/03/2025 0:00 35
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.
- reddevil
Thank you, this works.
- Ritaf1983
Hi reddevil
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- reddevil
Test Frequency Total Overdue count Last Test Date Next Test Date No of months old Six Monthly 1 16/12/2022 0:00 16/05/2023 0:00 26 Six Monthly 4 21/08/2024 0:00 31/07/2025 0:00 6 Six Monthly 0 4/09/2024 0:00 12/03/2025 0:00 5 Six Monthly 0 20/11/2024 0:00 20/05/2025 0:00 3 Quarterly 4 4/06/2024 0:00 6/06/2025 0:00 8 Quarterly 0 19/12/2024 0:00 14/04/2025 0:00 2 Every 18 Months 0 27/02/2024 0:00 28/08/2025 0:00 12 Biennial 1/08/2021 0:00 1/08/2023 0:00 42 Biennial 30/09/2021 0:00 30/09/2023 0:00 41 Biennial 29/10/2021 0:00 31/10/2023 0:00 40 Biennial 31/10/2021 0:00 31/10/2023 0:00 40 Biennial 29/11/2021 0:00 39 Biennial 1/12/2021 0:00 1/12/2023 0:00 38 Biennial 1/01/2022 0:00 1/01/2024 0:00 37 Biennial 28/02/2022 0:00 31/01/2024 0:00 36 Biennial 28/02/2022 0:00 28/02/2024 0:00 36 Biennial 2 3/03/2022 0:00 28/02/2026 0:00 35 Biennial 7/03/2022 0:00 28/02/2024 0:00 35 Biennial 10/03/2022 0:00 28/03/2024 0:00 35 Biennial 30/03/2022 0:00 28/03/2024 0:00 35 Biennial 31/03/2022 0:00 31/03/2024 0:00 35 Biennial 31/03/2022 0:00 1/04/2024 0:00 35 Biennial 2 31/03/2022 0:00 31/03/2025 0:00 35
