Forum Discussion
Relative Date filter, working with weeks.
Dears,
I tryed to search the answer here, in forum, but without success..
I have a column chart with the amount of signed docs W29, W30, ....
Need to show the data of the last 8 calendar weeks INCLUDING today, this week. "Include today" option is only available in Relative Date <Weeks>, not in <Calendar weeks>. I can't use <Weeks> because it shows kind of messy data then. Maybe there is some DAX code to measure? I already have calendarauto table with all week no. and etc created.
Here is 8 last week shown as <Calendar weeks>, but I need to include and week W37 and automatically remove W29.
Thank you.
Guys, I manage it!
Super super easy and clear video:https://www.youtube.com/watch?v=gGNxKluPFNw
Just with one column now I have my custom relative date filter!
Thank you all for your time.
7 Replies
gduobaite , One way is to use relative date slicer
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
If now. You need to have a date table with Week and Week Rank. And you will another table for display. Long way.
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense) // In your case it week number with Year
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
But this rolling will group data at one place
Try this
amitchandak, Relative date slicer is not working as I need - calendar week does not include today, while week option includes today, but it starts counting the week from random day.
I tryed your code and have issues with the first one:
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense) // In your case it week number with Year
My week number with year is a column, not a messure, and Week Rank, as I understand, requires messure with that. So I can't get it.
gduobaite , no it is a column. Rank can be on a column of Measure
Power BI — 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-Last-Week/ba-p/1051123Please check that video of the last 12-month rolling I shared. Without that solution will not be complete
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Have you considered creating a Week 'offset' column in your date table?
This is essentially a column that marks the current week as "0" (so W37 = 0 ) and then as you go back in time each week it subtracts 1 (W36 = -1, W35 = -2)
Then you can filter your visualization against this offset column. (Where week offset >= -8 and week offset <= 0).
It becomes your own home grown relative date filter.
Here are a few links for building an offset column (this can be done in power query or with DAX).
https://www.oraylis.de/blog/how-to-create-relative-week-column-in-power-bi
https://www.youtube.com/watch?v=NKr2TizM-KU
Guys, I manage it!
Super super easy and clear video:https://www.youtube.com/watch?v=gGNxKluPFNw
Just with one column now I have my custom relative date filter!
Thank you all for your time.
I now this is already solved but I'm offering an alternative solution for those who might search and find this thread.
https://www.villezekeviking.com/dax-tables-calendar-and-time/
I have offset-columns from today that can be used to filter any report relative from today on days, weeks, months, quarters or years.
Unlike the relative date filtering, this can span any period, from historical data, over current periods and into the future.
