Forum Discussion
Rolling average monthly. Prob: Considering previous year[month].
Hi,
I'm trying to calculate rolling average, but facing problem when year changes.
It's considering previous year while calculation.
Finding rolling average of "Cost per pair -USD" and Costper pair usd = [Total Epenses] / [QTY]
It is showing .21 (Rolling Avg.) instead of .18 (Year changes, So fresh cal.), higlighted with red line. It is because of considering previous month result
I used Quick measure in power bi for rolling average.
Rolling Avg. = IF(
ISFILTERED('Dummy_Table'[ETA]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Dummy_Table'[ETA].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Dummy_Table'[ETA].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Dummy_Table'),
'Dummy_Table'[ETA].[Year],
'Dummy_Table'[ETA].[QuarterNo],
'Dummy_Table'[ETA].[Quarter],
'Dummy_Table'[ETA].[MonthNo],
'Dummy_Table'[ETA].[Month]
),
__DATE_PERIOD
),
CALCULATE([Cost per Pair], ALL('Dummy_Table'[ETA].[Day]))
)
)
Expected result.
When I tried in excel it look like. It was just simply with formula.
Instead of .21 I want .18, as year changes from 2017 to 2018.
7 Replies
- v-yuta-msft
Community Support
Hi dheer1688,
Which DAX formula are you using on excel side? And which one is your expected result?
Regards,
Jimmy Tao
- AnonymousNot applicable
Hi,
I used formula in excel.
Expected result :
Instead of .21 for Jan2018 it should show .18 as year changes from 2017 to 2018.
- Ashish_Mathur
Super User
Hi,
Assuming the following, try this measure:
- There is a calendar Table; and
- There is a relationship from the Date column of your Dummy_Table to the Date column of the Calendar Table
=CALCULATE([Cost per pair - USD],DATESYTD(Calendar[Date],"31/12"))
If this does not get the correct answer, then share the link from where i can download your PBI file.
- AnonymousNot applicable
Hi Sorry for replying late .
Here is dummy file..
https://drive.google.com/open?id=16pMRCfKbzWzZzQkDBr4l-eaGy6kxIFpL
- Ashish_Mathur
Super User