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

 

 

 

 

 

Need Help

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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'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.