Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MH3
Helper V
Helper V

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
SummerSeason-Sample.jpg

Sample Data: Winter Season
WinterSeason - Sample.jpg


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
amitchandak
Super User
Super User

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

View solution in original post

9 REPLIES 9
MH3
Helper V
Helper V

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?

Thanks for your Blog link @amitchandak 

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?

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

@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
Slicer2.jpg

@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 

 

https://www.youtube.com/watch?v=TYLKDPcvgK8
https://www.blue-granite.com/blog/create-a-dynamic-title-in-power-bi-updated

amitchandak
Super User
Super User

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

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? 
 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.