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 indeed work correct. However, I only want the working days. Can someone explaine how to fix this?

 

min date = MIN('date'[Date])

max date = MAX('date'[Date])

range = 
VAR s =
    MAX ( 'Table'[start date] )
VAR e =
    MAX ( 'Table'[end date] )
RETURN
    IF([max date]>=s&&[max date]<=e,IF([min date]>=s,"midlle","left"),IF([max date]>e,IF([min date]<=e,"right","na"))

whole days =
SWITCH (
    [range],
    "midlle", DATEDIFF ( [min date], [max date], DAY ) + 1,
    "left", DATEDIFF ( MAX ( 'Table'[start date] ), [max date], DAY ) + 1,
    "right", DATEDIFF ( [min date], MAX ( 'Table'[end date] ), DAY ) + 1,
    0
)

Thanks!

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.

1 Reply

  • 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.