Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mainer04401
Helper III
Helper III

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
V-pazhen-msft
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

3 REPLIES 3
Sam_Jarvis
Regular Visitor

Hi, I used the date range table here that @DataZoe recommended and it returns the time periods perfectly, the issue I am having is all of my sales data is returning the same total value.  I have linked to my date table which is linked to my MAX Date table.  I just can't figure this one out.  Is it the DAX code for my sales data perhaps?  

 

WW Total Sales = CALCULATE([WW Sales], 'WW Data'[Channel] = "Total", 'WW Data'[Store cluster] = "Total" )
 
Thanks in advance, I have lost days trying to figure this one out.
V-pazhen-msft
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.
DataZoe
Employee
Employee

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.