- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 👏
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
Proud to be a Super User! |
|
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for the fast response. The formula seems to return an empty column (see images below). Does this look correct?

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
08-15-2024 08:50 PM | |||
10-03-2024 07:12 PM | |||
07-30-2024 05:40 PM | |||
09-18-2024 07:33 AM | |||
09-27-2024 04:21 AM |
User | Count |
---|---|
22 | |
12 | |
10 | |
10 | |
8 |
User | Count |
---|---|
16 | |
15 | |
14 | |
12 | |
10 |