DAX

[Anonymous]

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:

[v-juanli-msft]

Hi @Anonymous 

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.

[Anonymous]

Hi Maggie, I tried this also, but it doesn't work.

Both 

Somme YTD 1 = TOTALYTD(SUM(Sheet1[ETP Mensuel]);Sheet1[Date deb mois].[Date])

Somme YTD 2 = CALCULATE(SUM(Sheet1[ETP Mensuel]);DATESYTD(Sheet1[Date deb mois].[Date]))

work fine as long as I do a SUM, but if I replace SUM by AVERAGE, it does a wrong calculation:

Both 

Moyenne YTD 1 = TOTALYTD(AVERAGE(Sheet1[ETP Mensuel]);Sheet1[Date deb mois].[Date])

Moyenne YTD 2 = CALCULATE(AVERAGE(Sheet1[ETP Mensuel]);DATESYTD(Sheet1[Date deb mois].[Date]))

give values like 142, 150, and so on, instead of the correct YTD means which are about 1570.

I see that in your example there is not the .[Date] after the date field, but this doesn't change the result.

But anyway thank you for your help, I will try to see why your solution and what I tried doesn't work.

Have a nice day,

Best regards,

Barbara

[v-juanli-msft]

Hi @Anonymous 

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

[v-juanli-msft]

Hi @Anonymous 

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.

[Anonymous]

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