Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@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 @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?
@MH3 , Refer my Blogs of WOW can help
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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
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
@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
@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()
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |