Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATEDIFF without weekends

Hi guys,   I had a different post in which I asked help with my formule for the days between two dates using four different dates. That is fixed... well not completely..   The formula below does i...
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Based on the formula above, i work out the days of weekend in these periods.

    weekend =
VAR right_weekend =
    CALCULATE (
        COUNT ( 'date'[Date] ),
        FILTER (
            'date',
            [Date] >= [min date]
                && [Date] <= MAX ( 'Table'[end date] )
                && WEEKDAY ( [Date], 2 ) >= 6
        )
    )
VAR left_weekend =
    CALCULATE (
        COUNT ( 'date'[Date] ),
        FILTER (
            'date',
            [Date] >= MAX ( 'Table'[start date] )
                && [Date] <= [max date]
                && WEEKDAY ( [Date], 2 ) >= 6
        )
    )
VAR midlle_weekend =
    CALCULATE (
        COUNT ( 'date'[Date] ),
        FILTER (
            'date',
            [Date] >= [min date]
                && [Date] <= [max date]
                && WEEKDAY ( [Date], 2 ) >= 6
        )
    )
RETURN
    SWITCH (
        [range],
        "right", right_weekend,
        "left", left_weekend,
        "midlle", midlle_weekend,
        0
    )

    final days = [whole days]-[weekend]
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi Anonymous 

Based on the formula above, i work out the days of weekend in these periods.

weekend =
VAR right_weekend =
    CALCULATE (
        COUNT ( 'date'[Date] ),
        FILTER (
            'date',
            [Date] >= [min date]
                && [Date] <= MAX ( 'Table'[end date] )
                && WEEKDAY ( [Date], 2 ) >= 6
        )
    )
VAR left_weekend =
    CALCULATE (
        COUNT ( 'date'[Date] ),
        FILTER (
            'date',
            [Date] >= MAX ( 'Table'[start date] )
                && [Date] <= [max date]
                && WEEKDAY ( [Date], 2 ) >= 6
        )
    )
VAR midlle_weekend =
    CALCULATE (
        COUNT ( 'date'[Date] ),
        FILTER (
            'date',
            [Date] >= [min date]
                && [Date] <= [max date]
                && WEEKDAY ( [Date], 2 ) >= 6
        )
    )
RETURN
    SWITCH (
        [range],
        "right", right_weekend,
        "left", left_weekend,
        "midlle", midlle_weekend,
        0
    )

final days = [whole days]-[weekend]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.