Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Yes it's correct and it works.
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
You don't connect to the file. You download it. And when you click the link you must wait for a while.
The Link does not provide the correct data and, Is it the correct link that has been attached
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |