Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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