cancel
Showing results for
Did you mean:
Helper IV

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?

1 ACCEPTED SOLUTION
Super User IV

@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]
)

Proud to be a Super User!

9 REPLIES 9
Helper IV

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?

Super User IV

@MH3 , Refer my Blogs of WOW can help

Proud to be a Super User!

Helper IV

But can you help me with this, Ihave to deliver it to the client,
Right now I have a slicer of Seasonal Week on my Weekly Report, and I have to create another Weekly report which will show only the Last Week Data based on filter of the calculated column which I had created.
True = last Week,
False = This Week,
False = rest of the weeks, how should I apply it to that report?

Super User IV

@MH3 , Take a slicer of the page-level filter and set it to true?

Proud to be a Super User!

Helper IV

@amitchandak
I have used that Column and applied the Basic filtering of True, with Seasonal Weeks, the silcer picking up data correct but it's not selecting that Week in the slicer and hence the data is not being filtered

ScreenShot

Super User IV

@MH3 , That is not possible. You have to show the week name on a card visual. The slicer will not show that. You can check for Dynamic Title

Proud to be a Super User!

Super User IV

@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]
)

Proud to be a Super User!

Helper IV

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?

Super User IV

@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()
)

Proud to be a Super User!

Announcements

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.