Week Reference Formula breaks at calendar year change...

[jkal4712]

Every year I run into this same problem I have a week reference column my data that identifies the last 6 weeks for WoW and 6 Week trends or averages.  At the begining of each calendar year the formula breaks and it takes 6 weeks to work again.  I know it is based off the highlighted line for YEAR.  Is there a way to make this same code work off of fiscal week vs calendar week? 

Week Reference =

VAR _Reference =

TODAY ()

VAR _CurrentWeek =

WEEKNUM ( _Reference, 1 )

VAR _ColumnWeek =

WEEKNUM ( [fisc_wk_strt_dt].[Date], 1 )

RETURN

IF (

YEAR ([fisc_wk_strt_dt].[Date]) <= YEAR ( _Reference ),

SWITCH (

_CurrentWeek - _ColumnWeek,

1, "Last Week",

2, "2 Last Week",

3, "3 Last Week",

4, "4 Last Week",

5, "5 Last Week",

6, "6 Last Week"))

Last week of data that pulled was the previous fiscal and since we are in Calendar 2022 it is not calculating previous year weeks.  

  

[amitchandak · Accepted solution]

@jkal4712 ,  Try like

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
[start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
[start week]<=Today()-14 && [end date]>=Today()-14,"2nd Last Week" ,
[start week]<=Today()-21 && [end date]>=Today()-21,"3rd Last Week" ,
[start week]<=Today()-28 && [end date]>=Today()-28,"4th Last Week" ,
[start week]<=Today()-35 && [end date]>=Today()-35,"5th Last Week" ,
[start week]<=Today()-42 && [end date]>=Today()-42,"6th Last Week" ,
[Week Name]
)

for measure refer

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[jkal4712]

amitchandak, 

Thank you very much for the reply code works great.  It displays the week name in the column if the week is not in the last 6 weeks.  How would I get it to display a blank/null value instead? 

[v-kkf-msft]

Hi @jkal4712 ,

You simply need to modify the formula of amitchandak:

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
[start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
[start week]<=Today()-14 && [end date]>=Today()-14,"2nd Last Week" ,
[start week]<=Today()-21 && [end date]>=Today()-21,"3rd Last Week" ,
[start week]<=Today()-28 && [end date]>=Today()-28,"4th Last Week" ,
[start week]<=Today()-35 && [end date]>=Today()-35,"5th Last Week" ,
[start week]<=Today()-42 && [end date]>=Today()-42,"6th Last Week" ,
BLANK()
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz

[jkal4712]

His response was perfect and the BLANK() edit was exactly what I needed.  Thank you both very much!