How to calculate "Average" data in DAX?

[EZV12]

Dear all,

I need help to calculate the Average data YTD/MTD in DAX. Here below please the Excel chart, the formula is simple in Excel to calculate the average data since January till the month to date, but how to transform it into DAX in PBI?

Data

January

February

March

April

May

June

July

August

September

October

November

December

Rate %

92,10%

92,70%

91%

Date

Average

January

92,10%

February

92,40%

March

91,93%

April

May

June

July

August

September

October

November

December

Thanks in advance for your help.

Best regards

Rachel

[selimovd]

Hey @EZV12 ,

do you have a date table?

If yes, then you can ust the following function:

Average YTD =
CALCULATE(
    AVERAGE( myTable[myColumn] ),
    DATESYTD( DateTable[Date] )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[EZV12]

Hello Denis,

Thanks for your reply, however, in my data table, in one column, there are different values for different indicators. I need to calculate the cumulative average data YTD for each indicator. Here below please find a sample chart: 

How to modify your DAX formula accordingly? Thanks.

[selimovd · Accepted solution]

Hey @EZV12 ,

it would be helpful if you show the tables in Power BI and not in Excel. Like this I don't know how the data looks and can just guess how it would work.

This indicator you have to add to your measure then:

Average YTD Rate 1 =
CALCULATE(
    AVERAGE( myTable[myColumn] ),
    myTable[Indicators] = "Rate 1",
    DATESYTD( DateTable[Date] )
)

The same then for rate 2.

By the way, when you reply, make sure to link me @EZV12 , otherwise I will not be notified when you answer.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[EZV12]

Hello Denis,

It works! Many thanks!

Best regards

Rachel