Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
Solved! Go to Solution.
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 )
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]
)
Hi @gmsamborn ,
thanks for your time
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] )
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.
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.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
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:
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 )
@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
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
100 | |
64 | |
44 | |
37 | |
35 |