cancel
Showing results for
Did you mean:
Helper III

## How to calculate "Average" data in DAX?

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

Best regards

Rachel

1 ACCEPTED SOLUTION
Community Champion

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.

``````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

4 REPLIES 4
Community Champion

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

Helper III

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.

Community Champion

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.

``````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

Helper III

Hello Denis,

It works! Many thanks!

Best regards

Rachel

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.