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

Adding dynamic columns in date table for last 4 weeks, last 13 weeks, etc to use as filters

My data is at weekly grain and I've setup a custom date table by week.  I've included columns such as calendar year, quarter, month, etc, but users would like buttons that filter to last 4 weeks or last 13 weeks rather than adjusting the "between" date slicer.  

 

I'm open to ideas on how to do this (the simpler the better) but my thought is that I could accomplish this by adding columns to the date table, such as "last 4 weeks" where the last 4 weeks are populated and do the same thing in another column for last 13 weeks.  But I wouldn't want to manually adjust that every week when the data is refreshed. 

 

Is there some kind of formula that I can put into query editor or write in DAX that identifies the most recent 4 weeks and most recent 13 weeks?  And here's the key - NOT last 4 weeks as of TODAY, which is built into Power BI's time intelligence, but is useless if your data is not updated daily.  Instead, I need the last 4 weeks of data that is actually in the model.  (We have about a 2 week lag)

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

@Mainer04401 

Basically you need a column, enter a slicer table [Last  N Weeks], and a measure.

 

Last # week column = WEEKNUM(TODAY())-WEEKNUM([Date],2)

Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Last # week]<=SELECTEDVALUE('Slicer Table'[Last  N Weeks])))

 
I created a sample to demostrate how to filter the last n weeks. 
 
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Super User I
Super User I

@Mainer04401 You can create an additional calculated table ( Modeling -> New Table), and then join this on date to your date table. 

 

 

Date Ranges = 
var _sd = min('Date'[Date])
var _ed = max('Date'[Date])

return
UNION ( 
ADDCOLUMNS( CALENDAR(_sd-7,_sd),"Date Range","Last 7 Days","Sort",2) ,
ADDCOLUMNS( calendar(_sd-30,_sd),"Date Range","Last 30 Days","Sort", 3) ,
ADDCOLUMNS( calendar(_sd-90,_sd),"Date Range","Last 90 Days","Sort",4) ,
ADDCOLUMNS( calendar(_sd-180,_sd),"Date Range","Last 6 Months","Sort",5) ,
ADDCOLUMNS( calendar(_sd,_ed),"Date Range","All","Sort",1) 
)

 

The sort is so if you put it in a chiclet slicer like I do, you can have control over the order they list. You just have to do Sort By Column on the "Date Range" column by the "Sort" column.

 

Here I have it as min/max of the date table, but you would probably pick the fact table where you can get the latest date the data is as of. 

Community Support
Community Support

@Mainer04401 

Basically you need a column, enter a slicer table [Last  N Weeks], and a measure.

 

Last # week column = WEEKNUM(TODAY())-WEEKNUM([Date],2)

Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Last # week]<=SELECTEDVALUE('Slicer Table'[Last  N Weeks])))

 
I created a sample to demostrate how to filter the last n weeks. 
 
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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