Dynamically display last x quarters - line graph

[devika]

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 quarter starts in 20232, the line graph should show from 20183 to 20232 and so on.

The dataset is connected directly to the Oracle SQL server, storing 130000 rows.

Currently, in the measure, future quarters are truncated.

CHECK=

VAR MinDate =MinX(allselected(Merge[CASE_DATE]),Merge[CASE_DATE])

VAR MaxDate = MaxX(allselected(Merge[CASE_DATE]),MergeCASE_DATE])

RETURN CALCULATE(IF(MAX('Merge_Annual_Mx_Vss'[CASE_DATE]) < MinDate || MAX(Merge[CASE_DATE]) > MaxDate,BLANK(),value))

I have looked into this thread, but it is slow to churn. Rolling QuarterEnd is not working.

https://community.powerbi.com/t5/Desktop/Dynamically-display-last-8-quarters/m-p/394240#M179827

Any help would be grateful.

[amitchandak]

@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 · Accepted solution]

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)

))