Forum Discussion

jkal4712's avatar
jkal4712
Frequent Visitor
4 years ago
Solved

Week Reference Formula breaks at calendar year change...

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.  

  

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

    • jkal4712's avatar
      jkal4712
      Frequent Visitor
      4 years ago

      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's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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