Dynamic date period Filter based on slicer selection

[Anonymous]

Dear all,

I have 3 separate matrix tables that I'd like to show different date periods based on a single slicer selection. 

I followed Alberto's guide in the below to create the measures, have my relationships and date tables set up exactly the same way but when I try to apply the visual specific filter it filters to all the dates available in my calendar table rather than the required date period.

https://www.youtube.com/watch?v=d8Rm7dwM6gc 

This is the measue that's being used as a calculation group then acts as a visual level filter:

VAR NumOfMonths = -6
VAR ReferenceDate = MAX ( 'Date'[Date] )
VAR PreviousDates = DATESINPERIOD ( 'Previous Date'[Date], ReferenceDate, NumOfMonths, MONTH )
VAR Result =
CALCULATE (
SELECTEDMEASURE (),
REMOVEFILTERS ( 'Date' ),
KEEPFILTERS ( PreviousDates ),
USERELATIONSHIP ( 'Previous Date'[Date], 'Date'[Date] )
)
RETURN Result

Could be the reason it doesn't work in my case because I have a mix of both measures and aggregations in my table?

Any suggestions or help is more than welcome 

Thank you

Tom

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to "Dynamic date period Filter based on slicer selection" and i check your dax  i think you want to show the last n months data in your visual . Right?

Here are the steps you can refer to :
(1)This is my test data:

I create a date table like this:

(2)We can create a "what-if" parameter as a slicer:

(3)Then we can create a measure:

Measure = var _slicer = [Parameter Value]
var _curdate = MAX('Calendar'[Date])
var _max_date = MAXX( ALLSELECTED('Table') , [Date])
var _min_date = EOMONTH( _max_date , -_slicer)+1
 return
 IF(_slicer=BLANK(),1,  IF(_curdate>= _min_date && _curdate<=_max_date ,1 ,0))

(4)Then we can put the measure on the "Filter on this visual" and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @v-yueyunzh-msft ,

Thank you so much for the above, only modification I'd need to make to the code would be to use a date slicer and not a numeric value. How would you do that? 

Below is an example of my dashboard - sorry I had to block out most parts but hopefully still makes sense

At the moment by changing 'Reporting week' using the slicer the middle 3 columns update to that week and what I'd like to achieve is for the Actuals to filter to -4 weeks of the selected date and the Forecast to filter +5 weeks, so when adjusting the slicer all the 3 sections would filter accordingly on a 'date roll' basis. At the moment I have to set the values manually for the two side tables using a visual level filter. (Actuals and Forecast are separate tables / visuals)

Thank you so much

Tom

[amitchandak]

@Anonymous , The way I do it is to create a slicer on an independent date table and create measures like

show last 12 months

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

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

[LindaO713]

Hi Amit - I was wondering if there was a way to modify this to reflect the same period last year. I'm trying to compare "Current Qtr" to "Prior Qtr", which works perfectly. I'm trying to create "Same Qtr Last Year" measure but I'm having issues adapting this code to reflect this. For example, if Q1'2024 is selected, "Same Quarter Last Year" should reflect Q1'2023 dates. I'd greatly appreciate any suggestions you  might have. Thank you 🙂

[Anonymous]

Hi @amitchandak 
Thank you for the above, I'm afraid though it wouldn't resolve my issue because [Value] in the below statement would still refer to a column or one specific measue however, in my matrix table I have ~15 columns + about 5 measures therefore the function could only apply to one. What could I do?

---

@amitchandak wrote:

new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

---

Thank you