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

     

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