cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MH3
Helper IV
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
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 IV
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]
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

9 REPLIES 9
MH3
Helper IV
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?

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?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
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]
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

 


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors