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.
I am able to get the 12-month rolling average using this formula:
Amount-12MRAvg =
IF(
ISFILTERED('Orders'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
IF(
MIN('Orders'[Amount]) <> BLANK(),
VAR __LAST_DATE = ENDOFMONTH('Calendar'[Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Calendar'[Date].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -11, MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Orders'),
'Calendar'[Date].[Year],
'Calendar'[Date].[QuarterNo],
'Calendar'[Date].[Quarter],
'Calendar'[Date].[MonthNo],
'Calendar'[Date].[Month]
),
__DATE_PERIOD
),
CALCULATE(SUM('Orders'[Amount]), ALL('Calendar'[Date].[Day]))
),
BLANK()
)
)
I am able to plot a line chart with the Calendar[Date] as the axis and the Rolling Average as the value. But I have a requirement to use the 12-month rolling average values of two different attributes and divide them. How do I do that?
When I try to just show the current month's 12-month rolling average value, I only get a Blank cell. Why is that?
Ideally, if I can get the current month's 12-month rolling average values for the two attributes (Orders and Assets), then I can divide the values to show the actual metric. What am I doing wrong?
Thanks.
Solved! Go to Solution.
Hi @Anonymous ,
Try this measure to calculate the Rollig average for 12 month:
Measure=
Var Rollingsum= CALCULATE(SUM('Orders'[Amount]), DATESINPERIOD(Calender[Date], LASTDATE(Calender[Date]), -12, MONTH))
RETURN DIVIDE(Rollingsum, 12)
I hope this helps!
Mark this as a solution if I answered your question. Kudos are always appreciated.
Thanks!
Hi @Anonymous ,
Try this measure to calculate the Rollig average for 12 month:
Measure=
Var Rollingsum= CALCULATE(SUM('Orders'[Amount]), DATESINPERIOD(Calender[Date], LASTDATE(Calender[Date]), -12, MONTH))
RETURN DIVIDE(Rollingsum, 12)
I hope this helps!
Mark this as a solution if I answered your question. Kudos are always appreciated.
Thanks!
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |