One filter for Week and Past Four Weeks

[richard_jackson]

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

[richard_jackson · Accepted solution]

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

[Anonymous]

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]

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

[amitchandak]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[richard_jackson]

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]

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