cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

Super User

@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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

New Member

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.