Forum Discussion

MH3's avatar
MH3
Icon for Helper V rankHelper V
5 years ago
Solved

Showing Last Week as true in a Calculated Column

Hello, 

I have a calender table in DAX, which
  Starts from: May
  Ends On:   April

I have Fiscal Weeks which is  May's First week from Monday to Sunday is being counted as 1 FiscalWeek.

and Seasonal Weeks is calculated by dividing the months into two halfves or Seasons (because the client see their data into seasons).
Note: each Half have 27 Weeks , like 
Summer Season = 27 Weeks, Winter Season = 27 Weeks
May - Oct = Summer Season
Nov - April = Winter Season

Sample Data : SUMMER SEASON


Sample Data: Winter Season


Problem:  I want to create a calculated column of a Seasonal Last Week which will be true if , the Today's date is 15 Feburary, so it will match up with the date column and it will check in the seasonal week column in which the date lies in, then it will get the previous seasonal week from Monday to Sunday for instance its 8 Feburary to 14th Feburary (Monday to Sunday)  which is Seasonal Week 16 in Winter Month of Feburary. and the 15th Feburary 2021 lies in Seasonal Winter Week 17.

it should be dynamic and change over every Monday, because Monday is the WeekStart. 

So, for 15th Feburary, Previous week is  Week 8 - 14th, Seasonal Week is 16th, then the Last Seasonal Week will be True for this week and it will show false for the rest of the rows.
ImkeF 
Any Help? 

Need Help
Show and Tell
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    5 years ago

    MH3 , Based on what I got you can have columns like these. Merge using var if needed

     

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday Start
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

     

    Week Type = Switch( True(),
    [start week]<=Today() && [end date]>=Today(),"This Week" ,
    [start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
    [Week Name]
    )

9 Replies

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

    MH3 , Based on what I got you can have columns like these. Merge using var if needed

     

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday Start
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

     

    Week Type = Switch( True(),
    [start week]<=Today() && [end date]>=Today(),"This Week" ,
    [start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
    [Week Name]
    )

    • MH3's avatar
      MH3
      Icon for Helper V rankHelper V
      5 years ago

      Thanks for your reply amitchandak ,

       

      But I am a bit confused in your solution, like I want an output in calculated column as true or false.

      as I explained, the today's date lies in week 17 of Seasonal Winter Week of Feburary starting from 15th of feburary.

      So, the Previous Seasonal winter week is 16th which I have calculated in Seasonal Week Column in my calendar. So, for the 16th week Monday to Sunday 7 rows will be shown as true in the Last Week Seasonal Column and the rest will be False.

      My columns are Date | Day Name | Seasonal Week (1 - 27 for Winter Season, 1 - 27 for Summer Season) and so on..
       can you transform your logic as per my requirement? 
       

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

        MH3 , You can try like

         

        Week Type = Switch( True(),
        [start week]<=Today() && [end date]>=Today(),"This Week" ,
        [start week]<=Today()-7 && [end date]>=Today()-7,true() ,
        false()
        )

         
  • MH3's avatar
    MH3
    Icon for Helper V rankHelper V
    5 years ago

    Thanks amitchandak ,
    Working Great!!

    It shows Winter Week 16 as Last Week, and Winter Week 17 as This Week.

    I need little help over here

    But, I want to apply this column as a filter to a weekly report and send that weekly report on every monday and they can see the last week filtered report due to this column.

    Because, Power BI Email Subscription don't send the report in the last week selected slicer, it always send the report in the slicer which were selected before to counter this problem I used this last week logic and use that column as a filter which will always be filtered with the last week data.

    but I dont' see any data when I applied it as a filter any ideas?