topic Re: Pivot Grand Total ignores prior year in TOTALYTD Dax in DAX Commands and Tips

Pivot Grand Total ignores prior year in TOTALYTD Dax

moliveira-GA — Tue, 22 Mar 2022 18:24:55 GMT

Hi folks,

I'm curious about this little issue. That's not a real problem at all but, it seem I'm forgetting something. Note the column "Real_netRevenue_YTD" in the pivot below:

I'm using the line: TOTALYTD( [real_net_revenue] ) ; fCalendar[Date] ; ALLSELECTED( fCalendar ) )

The results for each year (lines) are correct, but the grand total in the pivot seems to ignore the prior years, and by that, doesn't calculate the total (note the Grand Total sum. It's equal to the 2022 year).

Did you guys saw this happen before?

Thanks!

Re: Pivot Grand Total ignores prior year in TOTALYTD Dax

amitchandak — Wed, 23 Mar 2022 03:59:56 GMT

moliveira-GA , you should only use

TOTALYTD( [real_net_revenue] ; fCalendar[Date])

fCalendar should be marked as date table

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Pivot Grand Total ignores prior year in TOTALYTD Dax

amitchandak — Wed, 23 Mar 2022 03:59:59 GMT

moliveira-GA , you should only use

TOTALYTD( [real_net_revenue] ; fCalendar[Date])

fCalendar should be marked as date table

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs

Re: Pivot Grand Total ignores prior year in TOTALYTD Dax

moliveira-GA — Wed, 23 Mar 2022 11:32:16 GMT

Hello amitchandak,

thanks for the answer but, unfortunally it doesn't worked. I've tried this way (TOTALYTD ([Real_net_revenue] ; fCalendar[Date]) and got the same result (yes, the fCalendar table is marked as date table).

I'm consulting the links you mentioned. thanks again.

Re: Pivot Grand Total ignores prior year in TOTALYTD Dax

v-yanjiang-msft — Fri, 25 Mar 2022 09:46:16 GMT

Hi moliveira-GA ,

According to your description, if you want the "Real_netRevenue_YTD" display the sum of this column at the total, try this measure:

Real_netRevenue_YTD = VAR _T = ADDCOLUMNS ( VALUES ( 'fCalendar'[Date] ), "YTD", TOTALYTD ( [real_net_revenue], fCalendar[Date] ) ) RETURN IF ( HASONEVALUE ( 'fCalendar'[Date] ), TOTALYTD ( [real_net_revenue], fCalendar[Date] ), SUMX ( _T, [YTD] ) )

If it still has other problems, please feel free to let me know.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.