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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
LindaO713
Frequent Visitor
2 years ago

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 🙂