Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic date period Filter based on slicer selection

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 m...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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