cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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],
__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.

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
7 REPLIES 7
Super User

Hi,

Assuming the following, try this measure:

1. There is a calendar Table; and
2. 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"))

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Hi Sorry for replying late .

Here is dummy file..

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@Ashish_Mathur

Thanks for helping me out.

It work like a charm. Salute man.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi

Which DAX formula are you using on excel side? And which one is your expected result?

Regards,

Jimmy Tao

Anonymous
Not 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.