Forum Discussion

devika's avatar
devika
Icon for Helper II rankHelper II
3 years ago
Solved

Dynamically display last x quarters - line graph

Hi all, I need to display the last 19 quarters from the current quarter dynamically.   The current quarter is 20231; the line graph should start showing from 20182 to 20231 and When the new quart...
Need Help
  • devika's avatar
    devika
    3 years ago

    Thank you for your time,

    I made a slight tweak as it wasn't displaying the current quarter.

     

    Last Qtr Today =
    var _today = today()
    var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
    var _min = eomonth(_max,-3*8)+1

    var _maxDate = [MaxCaseDate]


    return
    CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <=_maxDate)

    ))

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

devika , In case there is no date filter

 

last 8 qtr

 

Last Qtr Today =
var _today = today()
var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
var _min = eomonth(_max,-3*8)+1
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

if you want select a qtr in slicer, then that slicer should come from an independent date table 

 

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -24) +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

devika's avatar
devika
Icon for Helper II rankHelper II
3 years ago

Thank you for your time,

I made a slight tweak as it wasn't displaying the current quarter.

 

Last Qtr Today =
var _today = today()
var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
var _min = eomonth(_max,-3*8)+1

var _maxDate = [MaxCaseDate]


return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <=_maxDate)

))