Calculate Average year to date in Power BI

Question

I have a challenge in power BI to calculate average YTD.

I am looking for make the average for month two take in consideration data from month one and so on.

Any idea how to do that in Power BI?

Thank you in advance, Mahmoud

I think this [post](https://stackoverflow.com/questions/49430400/ytd-average-in-dax) show actually what you want? — TimvZon, Mar 22 '19 at 13:52
I did tried but it didn't work. Anyone can explain or advise please? — Mahmoud Samir, Mar 22 '19 at 16:01

score 1 · Accepted Answer · answered Mar 25 '19 at 08:04

Not knowing why the solution by Alexis didn't work for you, I've made the assumption that you only have one table (called 'Data'), with two columns as your image showed.

Step one: Add two new columns (if you do this in Power query or power bi doesn't matter): [Month_name] and [Month_number].

[Month_Name] = FORMAT([Date]; "MMM")

[Month_number] = MONTH([Date])

Step two: Add a measure

YTD Avg = 
var currentMonth = MAX([Month_number])
RETURN
DIVIDE(
    CALCULATE
        SUM(Data[Amount]);
        ALL(Data[Month_Name]);
        Data[Month_number] <= currentMonth
    );
    CALCULATE(
        COUNTROWS(DATA);
        ALL([Month_Name]);
        Data[Month_number] <= currentMonth
    );
    0
)

This should generate the following table:

YTD-Table

Hope this helps. Please don't hesitate to ask if you have further questions.

Thank you Oscar, I did managed to resolve the issue. Something was broken in my relationships. So, i resolved using Alexis function. But, great to have multiple ways. Thank you so much. :) — Mahmoud Samir, Mar 26 '19 at 10:56

Calculate Average year to date in Power BI

1 Answers1