Calculating the date of a day based on a week ending ... in Power BI Custom Column

[Aedmunds]

I have a table with Resource, Week Ending (date), Mon, Tues, Weds, Thu, Fri, Sat and Sun - the user enters the number of days worked for each column (M-F,S,S)

I want to unpivot the data ... so I have 7 records for each row ... and show basically the days per day (where the day is an actual date.

Week Ending (Sunday) - 08-08-2021

I want to create the date for each row for each day 

Mon

Tue

Wed

Thu

Fri

Sat

Then my unpivoted data will essentially have the date and the hours worked

If I have tried a nested IF statement ... but not too strong with Code ... so its not looking good and can't work out why.

"IF( [DayOfWeek] = "Mon" then [Week Ending]-6, else [DayOfWeek]="Tue" then [Week Ending]-5, else [DayOfWeek]="Wed" then [Week Ending]-4,else [DayOfWeek]="Thu" then [Week Ending]-3, else [DayOfWeek]="Fri" then [Week Ending]-2, else [DayOfWeek]="Sat" then [Week Ending]-1, else [Week Ending])"

Cheers

Andre

[Aedmunds]

Brilliant Mate!!!  😄 😄 😄

[Payeras_BI · Accepted solution]

Hi @Aedmunds ,

if [DayOfWeek] = "Mon" then Date.AddDays([Week Ending],-6) else if [DayOfWeek] = "Tue" then Date.AddDays([Week Ending],-5) else if [DayOfWeek] = "Wed" then Date.AddDays([Week Ending],-4) else if [DayOfWeek] = "Thu" then Date.AddDays([Week Ending],-3) else if [DayOfWeek] = "Fri" then Date.AddDays([Week Ending],-2) else if [DayOfWeek] = "Sat" then Date.AddDays([Week Ending],-1) else [Week Ending]