Need help in calculating Rolling quarter

[Anonymous]

Hi Team,

I have a table with fields Year, Quarter and Value. 

My requirement is, I want to add two filters on the report, year and quarter. If the user select year = 2022 and Quarter =Q1 then I need to display a bar graph with axis as selected quarter and previous 3 Quarters i.e, 2022-Q1, 2021-Q4, 2021-Q3, 2021-Q2.  It should change dynamically like...

if year=2021, Quarter= Q3 then Axis = 2021-Q3, 2021-Q2, 2021-Q1, 2020-Q4

if year=2022, Quarter= Q2 then Axis = 2022-Q2, 2022-Q1, 2021-Q4, 2021-Q3....so on

Can anyone help me with the formula to calculate last 3 quarters based on filter selection.

Thanks

[v-luwang-msft · Accepted solution]

HI @Anonymous ,

Pls refer the following steps:

1.create index on base table:

INDEX = RANKX('Table','Table'[Year]&""&RIGHT('Table'[Quarter],1),,ASC,Dense)

2.create another table ,the same as base table:

Table 2 = 'Table'

3.create the the following measure:

TEST1 = CALCULATE(MAX('Table 2'[INDEX]),ALLSELECTED('Table 2'))

TEST2 = CALCULATE(MAX('Table 2'[INDEX])-3,ALLSELECTED('Table 2'))

TEST3 = IF(MAX('Table'[INDEX])>='Table 2'[TEST2]&&MAX('Table'[INDEX])<='Table'[TEST1],1,0)

create visual:

You could download my pbix file to learn details.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Anonymous , if select one Qtr and want to display more than one then you need independent date table 

example

//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))

if you just want data of 4 qtr , 12 month

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

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