A new measure that calculates rolling average by Cost Center

[Anonymous]

I have the following table:

I want to calculate the monthly rolling average for Column Direct by CC. So for example the result should be as follows if month January and Feb are selected:

What I did to get this result is first created a new table with the following formula:

Which resulted in:

Then I created a new measure in this new table with the following formula:

I am now trying to get the same results using a measure in the original table. Without having to create the new summarized table.

Thanks,

[Ashish_Mathur]

Hi,

For calculating the Rolling average, why have you taken the denominator as 2 for each CC?  Also, this is just a simple average.  Why do you call it a rolling average?

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to "A new measure that calculates rolling average by Cost Center".

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)I create a date table like this:

Date = ADDCOLUMNS( CALENDAR( FIRSTDATE('Table'[Date]) ,LASTDATE('Table'[Date])) , "Year_month" , YEAR([Date])*100+MONTH([Date]))

(2)We can create two measures :

Direct Sum = SUMX('Table','Table'[Direct])

Direct Rolling Average = var _t =ADDCOLUMNS(CROSSJOIN( VALUES('Table'[CC]) ,VALUES('Date'[Year_month])) , "direct" , CALCULATE(SUM('Table'[Direct])))
return
AVERAGEX(_t , [direct])

(3)Then we can put the fields we need on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Padycosmos]

Perhaps this might help:

Rolling Average by CC =
CALCULATE(
AVERAGE(data[Column Direct]),
FILTER(
ALL(data),
data[month] >= MAX(data[month]) - 1
),
GROUPBY(data, data[CC], "Rolling Average by CC")
)

[IoannisPhilip]

Hi @Anonymous ,

maybe sth like the following can be useful:

Monthly_Rolling_Avg =
VAR sum_period = CALCULATE(SUM(table[DirectSum]), DATESINPERIOD(Table[Date]), LASTDATE[Table[Date]), 1, MONTH))
VAR month_in_period = CALCULATE(DISTINCTCOUNT(Table[Date]), DATESINPERIOD[Table[Date], LASTDATE(Table[Date]), 1, MONTH))
RETURN
sum_period / month_in_period

Alternatively, the kind of new function WINDOW(), might be handy.
I guess there are more efficient ways, but hopefully, this is working for you.

[Anonymous]

Thanks for your reply. But the formula doesn't summarize by cost center as well.

[Greg_Deckler]

@Anonymous See if this helps: Better Rolling Average - Microsoft Power BI Community

[Anonymous]

Thanks for sharing @gre2, 

the formula doesn't summarize by cost center as well. Which is the main struggle for me.