Date Variance Dax on same day not showing as 0

[job23]

Hi, I have a measure created with DAX to get the VAR of working days between the oldest bulk email date and todays date.

10 E Overdue Days = 
VAR STARTDATE = CALCULATE (
    MIN ('Emails'[Due_Date__c.1]),
    FILTER (ALL ( 'Emails'[Due_Date__c.1]), CALCULATE(COUNT('Emails'[Due_Date__c.1])>10))
    )
VAR ENDDATE = TODAY()
RETURN
    IF (
        STARTDATE < ENDDATE,
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    CALENDAR ( STARTDATE, ENDDATE ),
                    "Day of Week", WEEKDAY ( [Date], 2 )
                ),
                [Day of Week] <> 6
                    && [Day of Week] <> 7
            )
        ),
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    CALENDAR ( ENDDATE, STARTDATE ),
                    "Day of Week", WEEKDAY ( [Date], 2 )
                ),
                [Day of Week] <> 6
                    && [Day of Week] <> 7
            )
        )
    )

However I am finding that when the result is retrieved for the same day it still giving a value of 1 rather than 0. What do I need to add to this formula for it to return 0 for the same day?

Here is an example taken on 09/01/2023, ideally the two values for 9/01/2023 would be showing 0:

[v-jianboli-msft · Accepted solution]

Hi @job23 ,

When your start date and end date are the same day, Calendar() will still produce a table with only one day, so your result is 1 when they are the same day, if you want the result to be 0 for the same day, try this:

10 E Overdue Days =
VAR STARTDATE =
    CALCULATE (
        MIN ( 'Emails'[Due_Date__c.1] ),
        FILTER (
            ALL ( 'Emails'[Due_Date__c.1] ),
            CALCULATE ( COUNT ( 'Emails'[Due_Date__c.1] ) > 10 )
        )
    )
VAR ENDDATE =
    TODAY ()
RETURN
    SWITCH (
        TRUE (),
        STARTDATE < ENDDATE,
            COUNTROWS (
                FILTER (
                    ADDCOLUMNS (
                        CALENDAR ( STARTDATE, ENDDATE ),
                        "Day of Week", WEEKDAY ( [Date], 2 )
                    ),
                    [Day of Week] <> 6
                        && [Day of Week] <> 7
                )
            ),
        STARTDATE > ENDDATE,
            COUNTROWS (
                FILTER (
                    ADDCOLUMNS (
                        CALENDAR ( ENDDATE, STARTDATE ),
                        "Day of Week", WEEKDAY ( [Date], 2 )
                    ),
                    [Day of Week] <> 6
                        && [Day of Week] <> 7
                )
            ),
        STARTDATE = ENDDATE, 0
    )

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.