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 the past four weeks relative to the filtered week.

 

i.e. If I select Week 8, some graphs must show Week 8's data, others must show Week 5, 6, 7 and 8.

 

How might I achieve this - I'm able to filter by one week perfectly well, but am unsure how to build the "past four weeks" calculation.

 

Thanks!

 

Rich

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

     

6 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi richard_jackson ,

     

    Create an independent week slicer table and create measures like below:

    measure1 = IF(selectedvalue('table'[week]) = selectedvalue(slicer[week]),1,0)

measure2 = IF(selectedvalue('table'[week]) > selectedvalue(slicer[week])-4,1,0)

    Put these two measures into corresponding visual filter and set value =1.

     

    Best Regards,

    Jay

    • richard_jackson's avatar
      richard_jackson
      Frequent Visitor
      3 years ago

      Hi there - thanks, I've been on holiday this week so I'll look into this now!

       

      I'll let you know how I get on!

       

      Rich

  • richard_jackson's avatar
    richard_jackson
    Frequent Visitor
    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