Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
30 | |
22 |