topic Month plus month average in DAX Commands and Tips

Month plus month average

TsW08 — Thu, 11 Jul 2024 11:04:19 GMT

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 👏

Re: Month plus month average

bhanu_gautam — Thu, 11 Jul 2024 12:01:30 GMT

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

Re: Month plus month average

TsW08 — Thu, 11 Jul 2024 13:29:35 GMT

Hi bhanu_gautam

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

Re: Month plus month average

Anonymous — Fri, 12 Jul 2024 08:01:24 GMT

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.