Pivot Grand Total ignores prior year in TOTALYTD Dax

[moliveira-GA]

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!

[v-yanjiang-msft]

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.

[moliveira-GA]

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. 

[amitchandak]

@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-bd52912a5b...
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.

[amitchandak]

@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-bd52912a5b...
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.