Rolling 4 quarter sum for each selected quarter

[dragon_prince]

Hi,
I have data at snapshot date level.One snapshot date for in each quarter first day. I want to get the rolling 4 quarter for each quarter selected. Refer image below. r4q is the expected output. 
March is the Fiscal Quarter Start. 

Granularity of data - Snapshot Date x subscription id

input
snapshot_date	arr
01 March 2020	16
01 May 2020	11
01 May 2020	17
01 November 2020	19
01 February 2021	11
01 May 2021	16
01 August 2021	17
01 November 2021	15
01 February 2022	19
01 May 2022	12
01 August 2022	10
01 November 2022	15
Output
snapshot_date	r4q_arr
01 November 2021	59
01 February 2022	67
01 May 2022	63
01 August 2022	56
01 November 2022	56

[dragon_prince]

@amitchandak @bolfri @Idrissshatila 

[FreemanZ · Accepted solution]

hi @dragon_prince 

If you don't have a calendar table, you can also create a measure like this:

r4q_arr =

VAR _date =

MIN(TableName[snapshot_date])

RETURN

CALCULATE(

   SUM(TableName[arr]),

   TableName[snapshot_date]<=_date,

   TableName[snapshot_date]>=EDATE(_date, -12)

)

Plot a table visual with the date column and the measure.

[dragon_prince]

Hi @FreemanZ,

There is an issue here, 
If i am filtering for specific quarters, the correct value is not populating. Say if i select 5 quarters, only the 4 and 5th quarter values will have the correct r4q values populating. The rest will only take the available quarter. 

The expected filter is even if select  4 dates, for the first date , it should show the R4Q arr even though the previous dates may not be selected. 

Input
snapshot_date	Sum of arr	r4q
01 August 2019	11	42
01 November 2019	19	61
01 February 2020	6	47
01 May 2020	10	46
01 August 2020	13	48
01 November 2020	15	44
01 February 2021	7	45
01 May 2021	15	50
01 August 2021	20	57
01 November 2021	9	51
01 February 2022	16	60
01 May 2022	7	52
01 August 2022	20	52
01 November 2022	13	56
Output
snapshot_date	Sum of arr	r4q
01 February 2022	16	60
01 May 2022	7	52
01 August 2022	20	52
01 November 2022	13	56

[FreemanZ]

hi @dragon_prince 

how do you perform the "filtering for specific quarters"?

[dragon_prince]

I have added a filter at the top, where i select 4 snapshot quarters. 

[pratyashasamal]

Hi @dragon_prince ,
Try this calculation:-
CALCULATE([Sales QTD],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))
Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[dragon_prince]

@ Tried this, 
It is returning the same value in arr.

Formula for Measure used 

r4q2 = CALCULATE(sum('Table'[arr]),

          DATESBETWEEN('Calendar (2)'[Date]

                       ,EDATE(MIN('Calendar (2)'[Date]),-9)

                       ,MAX('Calendar (2)'[Date])))