Datediff with multiple criteria

[reddevil]

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" )
)

[Anonymous · Accepted solution]

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.

[danextian]

Hi @reddevil 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind.

[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

[rajendraongole1]

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.

[reddevil]

Thanks @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