cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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. 

gduobaite_0-1599545829662.png

 

Thank you. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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. 

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

@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



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!

Highlighted

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

 

Highlighted
Highlighted

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

Highlighted
Continued Contributor
Continued Contributor

@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

 

Highlighted

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. 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors