Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
veemo
Frequent Visitor

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

powerBi_example.jpg

 

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:

powerBi_example2.jpg

 

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

 

 

 

 

 

1 ACCEPTED SOLUTION
veemo
Frequent Visitor

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  

View solution in original post

4 REPLIES 4
Baskar
Resident Rockstar
Resident Rockstar

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
Frequent Visitor

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
Resident Rockstar
Resident Rockstar

great dude. Lets rockzzz. cheers 

veemo
Frequent Visitor

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  

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.