Forum Discussion

veemo's avatar
veemo
Frequent Visitor
9 years ago
Solved

TOTALYTD not working as expected

Hello everyone,

 

I'm new to PowerBi and DAX so please bear with me.

 

Here's an example of the table I'm working on

 

Now, I would like to calculate TOTALYTD so this is what I write in DAX

 

YTD Fatturato = TOTALYTD(SUM(PowerBI_VenditePerGgCittaFamTipocli[Importo]); PowerBI_VenditePerGgCittaFamTipocli[DataOrdine]; all(PowerBI_VenditePerGgCittaFamTipocli[DataOrdine]))

 

And I get:

 

which the sum of revenue by month. I would like to add up the value each month, each year (it goes until 2016) so to get the total. 

 

I thought the issue was with the dates, so I create a Date Table and used that instead, but with no improvement. 

 

What am I doing wrong?

 

Thanks a lot

 

 

 

 

 

Need Help
  • veemo's avatar
    veemo
    9 years ago

    I solved it

     

    I created a Date Table using http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

     

    Set the relationship with the date entry on my original table and then used the Date Table in DAX as

     

    TOTALYTD(SUM(PowerBI_VenditePerGgCittaFamTipocli[Importo]); DateTableM[Date]; all(DateTableM[Date]))

     

    I honestly do not know why it worked, I guess DAX needs to have a complete date/time reference to make calculations?

     

    Anyway, good to know. That Power query script sure looks useful  

4 Replies

  • veemo's avatar
    veemo
    Frequent Visitor
    9 years ago

    I solved it

     

    I created a Date Table using http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

     

    Set the relationship with the date entry on my original table and then used the Date Table in DAX as

     

    TOTALYTD(SUM(PowerBI_VenditePerGgCittaFamTipocli[Importo]); DateTableM[Date]; all(DateTableM[Date]))

     

    I honestly do not know why it worked, I guess DAX needs to have a complete date/time reference to make calculations?

     

    Anyway, good to know. That Power query script sure looks useful  

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi dude,

     

    YTD Fatturato = TOTALYTD(SUM(PowerBI_VenditePerGgCittaFamTipocli[Importo]); PowerBI_VenditePerGgCittaFamTipocli[DataOrdine])

     

    No need All with Table Name .. Check this one and let me if it is not help u .. i will help u

    • veemo's avatar
      veemo
      Frequent Visitor
      9 years ago

      Hi Baskar!

       

      That was the first thing I tried but it did not work.

       

      I guess TOTALYTD works only if you have the full set of dates in the given time. My date column was not as such. By appending a full date Table I was able to fill that gap. Or at least so I reckon.

       

      Anyway it is working pretty fine now. Thanks for your help!

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        great dude. Lets rockzzz. cheers