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

Regular Visitor

## How to calculate the average for each month from the beginning of the year?

Dear colleagues,

I can’t solve this problem, how to calculate the average from the beginning of the year for each month of this year and repeat this procedure for each next year?

For example I made a table in excel:

I try do it by DATESYTD, and with SUMX it works fine, but if I change SUMX in AVERAGEX, then I get a wrong values.

1 ACCEPTED SOLUTION
Super User

Hi @MaximB

It appears to me that you want an average based on a YTD total of monthly totals.
If that is the case, maybe try someting like this...

``````Monthly Average =
VAR _Yr = SELECTEDVALUE( 'Date'[Year] )
VAR _Mon = SELECTEDVALUE( 'Date'[MonthNo] )
VAR _Table =
FILTER(
SUMMARIZE(
ALL( 'Date' ),
'Date'[Year],
'Date'[MonthNo],
"@Total", SUM( 'Outstanding'[Outstanding] )
),
'Date'[Year] = _Yr
&& 'Date'[MonthNo] <= _Mon
)
VAR _Total =
SUMX(
_Table,
[@Total]
)
VAR _Count =
COUNTAX(
_Table,
[MonthNo]
)
VAR _Average =
DIVIDE(
_Total,
_Count
)
RETURN
IF( NOT ISBLANK( SUM( 'Outstanding'[Outstanding] ) ), _Average )``````

Monthly Averages - try 2.pbix

7 REPLIES 7
Super User

Hi @MaximB

Would something like this help?

``````Monthly Average =
AVERAGEX(
FILTER(
SUMMARIZE(
'Date',
'Date'[Year],
'Date'[Month],
"@Total", SUM( 'Outstanding'[outst] )
),
'Date'[Year] = SELECTEDVALUE( 'Date'[Year] )
),
[@Total]
)``````

Monthly Averages.pbix

Regular Visitor

Hi @gmsamborn ,

I tried to use your, measure and got a not entirely correct result, or rather just the sum

Tried to work with your measure a little:
Monthly Average = AVERAGEX( FILTER( SUMMARIZE( 'Data', 'Data'[Year], 'Data'[Month], "@Total", SUMX(Data,Data[Outstanding]) ), 'Data'[Year] = SELECTEDVALUE( 'Data'[Year] ) ), [@Total] )

Super User

Hi @MaximB

I think the main difference might be that you don't appear to be using a date table as your modified measure shows.

Super User

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

Regular Visitor

Hi @lbendlin ,
I prepared the expamle table with 4 different date:

 Date Product Prov_type Outstanding 30.11.2022 A 1 5 30.11.2022 B 1 10 30.11.2022 C 1 3 30.11.2022 A 2 4 30.11.2022 B 2 35 30.11.2022 C 2 62 30.11.2022 A 3 26 30.11.2022 B 3 23 30.11.2022 C 3 14 31.12.2022 A 1 12 31.12.2022 B 1 312 31.12.2022 C 1 3 31.12.2022 A 2 412 31.12.2022 B 2 35 31.12.2022 C 2 512 31.12.2022 A 3 865 31.12.2022 B 3 68 31.12.2022 C 3 568 31.01.2023 A 1 36 31.01.2023 B 1 10 31.01.2023 C 1 346 31.01.2023 A 2 466 31.01.2023 B 2 756 31.01.2023 C 2 385 31.01.2023 A 3 346 31.01.2023 B 3 23 31.01.2023 C 3 346 28.02.2023 A 1 5 28.02.2023 B 1 34 28.02.2023 C 1 63 28.02.2023 A 2 634 28.02.2023 B 2 35 28.02.2023 C 2 62 28.02.2023 A 3 96 28.02.2023 B 3 967 28.02.2023 C 3 678

And assume reuslt in the table under pivot:

Super User

Hi @MaximB

It appears to me that you want an average based on a YTD total of monthly totals.
If that is the case, maybe try someting like this...

``````Monthly Average =
VAR _Yr = SELECTEDVALUE( 'Date'[Year] )
VAR _Mon = SELECTEDVALUE( 'Date'[MonthNo] )
VAR _Table =
FILTER(
SUMMARIZE(
ALL( 'Date' ),
'Date'[Year],
'Date'[MonthNo],
"@Total", SUM( 'Outstanding'[Outstanding] )
),
'Date'[Year] = _Yr
&& 'Date'[MonthNo] <= _Mon
)
VAR _Total =
SUMX(
_Table,
[@Total]
)
VAR _Count =
COUNTAX(
_Table,
[MonthNo]
)
VAR _Average =
DIVIDE(
_Total,
_Count
)
RETURN
IF( NOT ISBLANK( SUM( 'Outstanding'[Outstanding] ) ), _Average )``````

Monthly Averages - try 2.pbix

Regular Visitor

@gmsamborn , thank you very much, this is what I need. I transferred the formula to my real data and now everything works. Thank you again, I have been struggling with this problem for several days

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.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors