Week 0

[redhughes]

Hi,

I have appointments data with a column for exact date and a column with "week commencing on..." (i.e. Monday date for that week). What I am hoping to achieve is an extra column that will say 1 and 2 for "future" weeks that I'm reporting on, 0 for the current week, and -1, -2 etc. for weeks in the past. Obviously, this should update every time I am adding new lines of data. When I was using Excel as the source of data, the formula that worked was based on the MAX of the week commencing column, but now I am unsure how to replicate purely in PowerBI.

Thanks in advance for all suggestions and advice!

[v-jiascu-msft]

Hi @redhughes,

Did these posts solve your issue?

Best Regards,

Dale

[redhughes]

Yes, thanks!

[NipponSahore]

You can create a Calculated column as 

WeekCommencingOn = WeekNum(apptDate,1) - WeekNum(today(),1)

[redhughes]

it could work, but it means week 0 will change based on what "today" is, correct? I'd rather it changed only when I add a new week's data.

[NipponSahore · Accepted solution]

you can modify the column as 

WeekCommencingOn = var recentmaxdate = max(appDate) returns WeekNum(apptDate,1) - WeekNum(recentmaxdate ,1)