Forum Discussion

Mainer04401's avatar
Mainer04401
Icon for Helper III rankHelper III
6 years ago
Solved

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)

Need Help

3 Replies

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.
  • Sam_Jarvis's avatar
    Sam_Jarvis
    Regular Visitor
    3 years ago

    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.