Rolling average monthly. Prob: Considering previous year[month].

[Anonymous]

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.

[Ashish_Mathur]

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"))

If this does not get the correct answer, then share the link from where i can download your PBI file.

[Anonymous]

@Ashish_Mathur@v-yuta-msft

Hi Sorry for replying late .

Here is dummy file..

https://drive.google.com/open?id=16pMRCfKbzWzZzQkDBr4l-eaGy6kxIFpL

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur

Thanks for helping me out.

It work like a charm. Salute man.

[Ashish_Mathur]

You are welcome.

[v-yuta-msft]

Hi dheer1688,

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

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft 

Hi,

I used formula in excel. 

Expected result :

Instead of .21 for Jan2018 it should show .18 as year changes from 2017 to 2018.