Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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)
Solved! Go to Solution.
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])))
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?
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])))
@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/
User | Count |
---|---|
88 | |
84 | |
67 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |