Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
mannes
Frequent Visitor

Quarterly Moving average with additional category scenario slicer in to effect

‎06-03-2022 04:22 AM

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 )

 

MonthYearDateQuarterCATEGORYAmount
JAN202001-01-2020Q120200
FEB202001-02-2020Q120200
MAR202001-03-2020Q12020400
APR202001-04-2020Q220200
MAY202001-05-2020Q220200
JUN202001-06-2020Q22020400
JUL202001-07-2020Q320200
AUG202001-08-2020Q320200
SEP202001-09-2020Q32020400
OCT202001-10-2020Q420200
NOV202001-11-2020Q420200
DEC202001-12-2020Q42020400
JAN202101-01-2021Q120210
FEB202101-02-2021Q120210
MAR202101-03-2021Q12021400
APR202101-04-2021Q220210
MAY202101-05-2021Q220210
JUN202101-06-2021Q22021400
JUL202101-07-2021Q320210
AUG202101-08-2021Q320210
SEP202101-09-2021Q32021400
OCT202101-10-2021Q420210
NOV202101-11-2021Q420210
DEC202101-12-2021Q42021400
JAN202201-01-2022Q12022 V10
FEB202201-02-2022Q12022 V10
MAR202201-03-2022Q12022 V1400
APR202201-04-2022Q22022 V10
MAY202201-05-2022Q22022 V10
JUN202201-06-2022Q22022 V1400
JUL202201-07-2022Q32022 V10
AUG202201-08-2022Q32022 V10
SEP202201-09-2022Q32022 V1400
OCT202201-10-2022Q42022 V10
NOV202201-11-2022Q42022 V10
DEC202201-12-2022Q42022 V1400
JAN202201-01-2022Q12022 V20
FEB202201-02-2022Q12022 V20
MAR202201-03-2022Q12022 V2400
APR202201-04-2022Q22022 V20
MAY202201-05-2022Q22022 V20
JUN202201-06-2022Q22022 V2400
JUL202201-07-2022Q32022 V20
AUG202201-08-2022Q32022 V20
SEP202201-09-2022Q32022 V2400
OCT202201-10-2022Q42022 V20
NOV202201-11-2022Q42022 V20
DEC202201-12-2022Q42022 V2400
JAN202201-01-2022Q12022 V30
FEB202201-02-2022Q12022 V30
MAR202201-03-2022Q12022 V3400
APR202201-04-2022Q22022 V30
MAY202201-05-2022Q22022 V30
JUN202201-06-2022Q22022 V3400
JUL202201-07-2022Q32022 V30
AUG202201-08-2022Q32022 V30
SEP202201-09-2022Q32022 V3400
OCT202201-10-2022Q42022 V30
NOV202201-11-2022Q42022 V30
DEC202201-12-2022Q42022 V3400

SLICERS

 

CATEGORYQUARTER
2020Q1
2021Q2
2022 V1Q3
2022 V2Q4
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

Labels:
Message 1 of 7
1,041 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable

‎06-08-2022 02:03 AM

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.

View solution in original post

Message 7 of 7
835 Views
0
6 REPLIES 6
Anonymous
Not applicable

‎06-08-2022 02:03 AM

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.

Message 7 of 7
836 Views
0
daXtreme
Solution Sage
Solution Sage

‎06-03-2022 12:50 PM

Yes it's correct and it works.

Message 4 of 7
944 Views
0
mannes
Frequent Visitor
In response to daXtreme

‎06-06-2022 03:22 AM

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

Message 5 of 7
880 Views
0
daXtreme
Solution Sage
Solution Sage
In response to mannes

‎06-06-2022 04:10 AM

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

Message 6 of 7
872 Views
0
daXtreme
Solution Sage
Solution Sage

‎06-03-2022 10:15 AM

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

Message 2 of 7
964 Views
0
mannes
Frequent Visitor
In response to daXtreme

‎06-03-2022 10:44 AM

mannes_0-1654278213397.png

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

Message 3 of 7
960 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All