TOTALYTD not working as expected

[veemo]

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

[Baskar]

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]

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!

[Baskar]

great dude. Lets rockzzz. cheers 

[veemo · Accepted solution]

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