Forum Discussion

New Member

2 years ago

Solved

Month plus month average

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

Anonymous

2 years ago

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.

Month plus month average.pbix27 KB

bhanu_gautam

Super User

2 years ago

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