Forum Discussion

richard_jackson's avatar
richard_jackson
Frequent Visitor
3 years ago
Solved

One filter for Week and Past Four Weeks

Hi there!   I have a Finance dashboard and have been asked to add a weekly filter. This filter must be used to reduce certain charts to using that week only, and other charts to be using data from ...
Need Help
Tutorial Requests
  • richard_jackson's avatar
    richard_jackson
    3 years ago

    This has now been resolved without using independent date tables:

     

    NewMeasure_FourWeekValues = 
    var _max = MAXX(ALLSELECTED('DateTable'),'DateTable'[Week])
    var _min = _max - 3
RETURN
    CALCULATE(SUM(ValueTable[Value]),FILTER(ALL('DateTable'), 'DateTable'[Week] >= _min && 'DateTable'[Week] <= _max))

     

     

     Thanks for the direction amitchandak & Anonymous !


    Rich

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

richard_jackson , if you want to select a week and want to show 4 weeks on axis/row then  you slicer on an independent table

 

then try like

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max -28
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

or based on week rank

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Week Rank])
var _min = _max -4
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Week Rank] >=_min && 'Date'[Week Rank] <=_max))

 

 

where week rank is a new column

 

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

 

 

If you need rolling 4 week

 

Last 4 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

richard_jackson's avatar
richard_jackson
Frequent Visitor
3 years ago

Hi Amit,

Thanks for this!

Would the first option (//Date1 is independent Date table, Date is joined with Table) provide a table where I can see the sum of each week?

i.e. If I had a graph of sales and I selected "Week 10", wanting to see the past four weeks, would I see sales values for week 7, 8, 9 and 10 on the same graph (for example, four separate columns)?

Thanks for your help and YT links!

 

Rich

  • richard_jackson's avatar
    richard_jackson
    Frequent Visitor
    3 years ago

    Hi amitchandak,

    Thanks for this:

     

    I'm using the first method, and I either get only one date, or, using all, the total SUM for each of the dates!

    1) Result for date selected by slicer
    NewMeasure =
    var _max = maxx(ALLSELECTED('Date'), 'Date'[Date])
    var _min = _max - 28
    return
    CALCULATE(
    SUM('Sales'[Sales]),
    FILTER('Date', 'Date'[Date] >= _min && 'Date'[Date] <= _max)
    )

     

    2) Result for dates before slicer, but all results given are the sum of all values
    NewMeasure =
    var _max = maxx(ALLSELECTED('Date'), 'Date'[Date])
    var _min = _max - 28
    return
    CALCULATE(
    SUM('Sales'[Sales]),
    FILTER(ALL('Date'), 'Date'[Date] >= _min && 'Date'[Date] <= _max)
    )

    Any advice on how to GROUPBY the dates over a date range which covers the 4 weeks (28 days) before the date selected in a slicer?

    TY!

    Rich