Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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] )
)
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.
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.
Hello Denis,
It works! Many thanks!
Best regards
Rachel
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |