Month plus month average

[TsW08]

Hi,

I have a table in Power Bi with all our staff, and costings. The tsblr has a "Date" column, as well as "Year", "Month" & "Quarter" and then  a "Cost" column.

I have been looking for a way to calculate the average "Cost" in a specific way but unable to find an answer.

What I need, is to sum for each month (for monthly cost totals)

Then

Average between month 1 & 2, 

Average between month 2 & 3 

And so on.... 

Then sum all the above average and divide by 12. 

•  Any help is much appreciated 👏

[v-jialongy-msft · Accepted solution]

Thanks for your prompt reply! @bhanu_gautam 

Hi @TsW08 

Based on your needs, I have created the following table.

Then you can try the following dax to get "total cost", "average between two months", "average divide by 12".

Total = 
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])

RETURN
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Year]=_year && 'Table'[Month]=_month))

Average = 
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])
VAR _current = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month))
VAR _previous = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month - 1))

RETURN IF(_previous = BLANK(),[Total],(_current + _previous)/2)

average and divide by 12 = 
var _year = SELECTEDVALUE('Table'[Year])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Year]=_year),'Table'[Average])/12

Result:

Best Regards,

Jayleny

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

[bhanu_gautam]

@TsW08 ,  Create a calculated column for monthly totals

MonthlyTotal =
CALCULATE(
SUM(StaffCostings[Cost]),
ALLEXCEPT(StaffCostings, StaffCostings[Year], StaffCostings[Month])
)

Create a calculated column for the moving average between consecutive months:

DAX
MovingAverage =
VAR CurrentMonth = StaffCostings[Month]
VAR CurrentYear = StaffCostings[Year]
VAR PreviousMonth = IF(CurrentMonth = 1, 12, CurrentMonth - 1)
VAR PreviousYear = IF(CurrentMonth = 1, CurrentYear - 1, CurrentYear)
VAR PreviousMonthTotal =
CALCULATE(
SUM(StaffCostings[MonthlyTotal]),
StaffCostings[Year] = PreviousYear,
StaffCostings[Month] = PreviousMonth
)
RETURN
IF(
ISBLANK(PreviousMonthTotal),
BLANK(),
(StaffCostings[MonthlyTotal] + PreviousMonthTotal) / 2
)

Create a measure to sum the moving averages and divide by 12

DAX
FinalAverage =
VAR MovingAverages =
CALCULATETABLE(
VALUES(StaffCostings[MovingAverage]),
NOT(ISBLANK(StaffCostings[MovingAverage]))
)
VAR SumOfMovingAverages = SUMX(MovingAverages, [MovingAverage])
RETURN
SumOfMovingAverages / 12

[TsW08]

 Hi @bhanu_gautam 

Thank you for the fast response. The formula seems to return an empty column (see images below). Does this look correct?