Quarterly Moving average with additional category scenario slicer in to effect

[mannes]

Hello Team,

I am new to Power BI,

I need to calculate Rollover moving quarterly average but I have multiple versions of data for same year (For eg : For 2022 itself I have V1,V2,V3 etc..), When I have to select the slicer as 2022 v2, then it should pick up the data pf 2022 V2 and go back quarterly for 2021 and 2020 and should not touch the data of other versions, Could you please help me on to it (I have sample data below )

Month	Year	Date	Quarter	CATEGORY	Amount
JAN	2020	01-01-2020	Q1	2020	0
FEB	2020	01-02-2020	Q1	2020	0
MAR	2020	01-03-2020	Q1	2020	400
APR	2020	01-04-2020	Q2	2020	0
MAY	2020	01-05-2020	Q2	2020	0
JUN	2020	01-06-2020	Q2	2020	400
JUL	2020	01-07-2020	Q3	2020	0
AUG	2020	01-08-2020	Q3	2020	0
SEP	2020	01-09-2020	Q3	2020	400
OCT	2020	01-10-2020	Q4	2020	0
NOV	2020	01-11-2020	Q4	2020	0
DEC	2020	01-12-2020	Q4	2020	400
JAN	2021	01-01-2021	Q1	2021	0
FEB	2021	01-02-2021	Q1	2021	0
MAR	2021	01-03-2021	Q1	2021	400
APR	2021	01-04-2021	Q2	2021	0
MAY	2021	01-05-2021	Q2	2021	0
JUN	2021	01-06-2021	Q2	2021	400
JUL	2021	01-07-2021	Q3	2021	0
AUG	2021	01-08-2021	Q3	2021	0
SEP	2021	01-09-2021	Q3	2021	400
OCT	2021	01-10-2021	Q4	2021	0
NOV	2021	01-11-2021	Q4	2021	0
DEC	2021	01-12-2021	Q4	2021	400
JAN	2022	01-01-2022	Q1	2022 V1	0
FEB	2022	01-02-2022	Q1	2022 V1	0
MAR	2022	01-03-2022	Q1	2022 V1	400
APR	2022	01-04-2022	Q2	2022 V1	0
MAY	2022	01-05-2022	Q2	2022 V1	0
JUN	2022	01-06-2022	Q2	2022 V1	400
JUL	2022	01-07-2022	Q3	2022 V1	0
AUG	2022	01-08-2022	Q3	2022 V1	0
SEP	2022	01-09-2022	Q3	2022 V1	400
OCT	2022	01-10-2022	Q4	2022 V1	0
NOV	2022	01-11-2022	Q4	2022 V1	0
DEC	2022	01-12-2022	Q4	2022 V1	400
JAN	2022	01-01-2022	Q1	2022 V2	0
FEB	2022	01-02-2022	Q1	2022 V2	0
MAR	2022	01-03-2022	Q1	2022 V2	400
APR	2022	01-04-2022	Q2	2022 V2	0
MAY	2022	01-05-2022	Q2	2022 V2	0
JUN	2022	01-06-2022	Q2	2022 V2	400
JUL	2022	01-07-2022	Q3	2022 V2	0
AUG	2022	01-08-2022	Q3	2022 V2	0
SEP	2022	01-09-2022	Q3	2022 V2	400
OCT	2022	01-10-2022	Q4	2022 V2	0
NOV	2022	01-11-2022	Q4	2022 V2	0
DEC	2022	01-12-2022	Q4	2022 V2	400
JAN	2022	01-01-2022	Q1	2022 V3	0
FEB	2022	01-02-2022	Q1	2022 V3	0
MAR	2022	01-03-2022	Q1	2022 V3	400
APR	2022	01-04-2022	Q2	2022 V3	0
MAY	2022	01-05-2022	Q2	2022 V3	0
JUN	2022	01-06-2022	Q2	2022 V3	400
JUL	2022	01-07-2022	Q3	2022 V3	0
AUG	2022	01-08-2022	Q3	2022 V3	0
SEP	2022	01-09-2022	Q3	2022 V3	400
OCT	2022	01-10-2022	Q4	2022 V3	0
NOV	2022	01-11-2022	Q4	2022 V3	0
DEC	2022	01-12-2022	Q4	2022 V3	400

SLICERS

CATEGORY	QUARTER
2020	Q1
2021	Q2
2022 V1	Q3
2022 V2	Q4
2022 V3

(For Example) - IfSelected category as 2022 V3 & quarter  Q3 (In slicer panel) - Rolling average of 4 quarters2022V3 Q3  + 2022 V3 Q2 +2022 V3 Q1 + 2021 Q4

[v-rzhou-msft · Accepted solution]

Hi @mannes ,

I suggest you to create a calculated column in "Amounts" table.

Version = IF(LEN(Amounts[Scenario])>4,RIGHT(Amounts[Scenario],2),BLANK())

Then create a measure to calculate average in last four quarters.

Measure = 
VAR _MAXDATE = MAX('Calendar'[Date])
VAR _4QBEFORE = EOMONTH(_MAXDATE,-12)+1
VAR _SELECT_VERSION = SELECTEDVALUE(Amounts[Version])
RETURN
CALCULATE(AVERAGE(Amounts[Amount]),FILTER(ALL(Amounts),Amounts[Date]>=_4QBEFORE&&Amounts[Date]<=_MAXDATE && Amounts[Version] = BLANK() ||Amounts[Version] = _SELECT_VERSION))

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Yes it's correct and it works.

[mannes]

Thank you so much for reply but I could not connect to the PBX file, If possible could you please share the PBI desktop file link,It would be really grateful

[daXtreme]

You don't connect to the file. You download it. And when you click the link you must wait for a while.

[daXtreme]

https://1drv.ms/u/s!ApyQEauTSLtOgaQT_bXeH6Qt_7hJmw?e=RB6hKg

[mannes]

The Link does not provide the correct data and, Is it the correct link that has been attached