Forum Discussion
Week 0
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!
you can modify the column as
WeekCommencingOn = var recentmaxdate = max(appDate) returns WeekNum(apptDate,1) - WeekNum(recentmaxdate ,1)
5 Replies
You can create a Calculated column as
WeekCommencingOn = WeekNum(apptDate,1) - WeekNum(today(),1)
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.
you can modify the column as
WeekCommencingOn = var recentmaxdate = max(appDate) returns WeekNum(apptDate,1) - WeekNum(recentmaxdate ,1)
- Yes, thanks!
