Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am new to Power BI, and for the moment trying DAX.
I was able do calculate a measure for the Year to Date sum of a value with following formula:
Somme YTD = TOTALYTD(SUM(Sheet1[ETP Mensuel]);Sheet1[Date deb mois].[Date])
So I thoughtt that it would be easy to simply use the same formula but replace SUM by AVERAGE. But this didn't work.
Moyenne YTD = TOTALYTD(AVERAGE(Sheet1[ETP Mensuel]);Sheet1[Date deb mois].[Date]) does not work
Finally I was able to do it like this:
Moyenne YTD = DIVIDE(TOTALYTD(SUM(Sheet1[ETP Mensuel]);Sheet1[Date deb mois].[Date]); MAX( Sheet1[Date deb mois].[NoMois] ) ;0)
This worked fine, but I find it not very elegant, and I would like to understand why it didn't work with TOTALYTD and AVERAGE functions.
Maybe I didn't get right what DAX does "behind the scene"...
A great thank you if anybody has an idea,
An extract of my data is below:
Hi @Barbara
If the screenshot shows the correct Moyenne YTD,
create a measure
Measure = CALCULATE(AVERAGE(Sheet4[ETP]),DATESYTD(Sheet4[date]))
You could learn more about DAX functions here.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Maggie, I tried this also, but it doesn't work.
Both
Hi @Barbara
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
Best Regards
Maggie
Hi @Barbara
I figure out why my solution doesn't fit on your side.
i think date format is mm.dd.yyyy.
But it seems your format should be dd.mm.yy.
Aslo, I test with a dataset including continuous date.
If your format is dd.mm.yy, and your table has discontinuous date, please create a new table
calendar = ADDCOLUMNS(CALENDARAUTO(),"year/month",FORMAT([Date],"yyyymm"))
Connect it to your table (create relationship between two tables)
Then create a measure in your table
Measure = CALCULATE(AVERAGE(Sheet4[ETP]),DATESYTD('calendar'[Date]))
You don't need to use [date].[date] format in your formula.
It may lead some errors.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Maggie,
sorry to answer late, I was busy with other things and had no time to check this solution.
I checked it just now and it does not work, but I think this is due to my data which is more complex (I gave only an aggregate sample in my post because I can't give the whole).
But I think that you pointed out the right problem which is surely related to date format.
I will try to work this out and I will let you know as soon as possible.
Thank you very much for this hint,
Best regards,
Barbara
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |