Relative Date filter, working with weeks.

[gduobaite]

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. 

[Villezekeviking]

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.

[swise001]

@gduobaite 

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://radacad.com/offset-columns-for-the-date-table-flexibility-in-relative-date-filtering-for-pow...

https://www.youtube.com/watch?v=NKr2TizM-KU

[gduobaite · Accepted solution]

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. 

[amitchandak]

@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

https://www.youtube.com/watch?v=duMSovyosXE

[gduobaite]

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

[amitchandak]

@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-La...

Please 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/3...

[gduobaite]

@amitchandak, thank you. 

I reached that video and everything went fine. Also, I managed to rank weeks as you wrote with those codes (one of your link helped me a lot).

But now I'm confused, how to implement weeks in my report as guy did with months in his video.