Week End on Thursday

[yaman123]

Hi,

I have a query to add a new column to show the week end date from a delivery date field. I have the below dax query but it isnt producing the correct results. The week runs from Fri - Thurs.

When a delivery date is a Fri, the week end date shows as the day before e.g Delivery Date is 05/11 but week end date shows as 04/11 when it should show as 11/11

Week End = 'KPI Service Level Query'[DELIVERY_CONFIRMED_DATE] - WEEKDAY('KPI Service Level Query'[DELIVERY_CONFIRMED_DATE],2)+4

Any help is appreciated!

[Anonymous]

Hi @yaman123 ,

Try this calculated column

Week End =
VAR _weekday =
    SWITCH (
        WEEKDAY ( [delivery date], 2 ),
        5, 1,
        6, 2,
        7, 3,
        1, 4,
        2, 5,
        3, 6,
        4, 7
    )
RETURN
    SWITCH (
        _weekday,
        1, [delivery date] + 6,
        2, [delivery date] + 5,
        3, [delivery date] + 4,
        4, [delivery date] + 3,
        5, [delivery date] + 2,
        6, [delivery date] + 1,
        7, [delivery date]
    )

The general rule is to first get the week days that start on Friday and end on Thursday, and then calculate the end date of the week separately.

Best Regards,

Stephen Tao

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

[amitchandak · Accepted solution]

@yaman123 , refer my blog on the same

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

Also, check if attached file after signature can help