TOTALYTD does NOT take cumulative

[QQQ]

TOTALYTD acts as sum for each time period rather than a cumulative.

I have a separate Calendar table in Power Pivot and I created relationship with my main table.

Here is the formula

YTD_Matches:=TOTALYTD(sum(Complete_Detailed_Incidents[Matches]),'Calendar'[Date])

When I create Pivot, below is what it looks like.  How to fix this?

Row Labels	YTD_Matches	Sum of Matches
Jan	3807632	3807632
Feb	1786060	1786060
Mar	504310	504310
Apr	609969	609969
May	1277743	1277743
Jun	3527579	3527579
Jul	2041383	2041383
Grand Total	13554676	13554676

[QQQ · Accepted solution]

Hello @nickchobotar @TomMartens @Interkoubess

Thank you all for the quick and helpful responses.

I just realized my Months in Pivot Table was from wrong table. Instead of getting it from Data table (which I named 'Calendar'), I got it from the Facts table.

Below is the updated table

[v-yulgu-msft]

Hi @QQQ,

Glad to hear that you have resolved the problem. Would you please kindly mark the helpful response and your resolution as an answer so that others having similar concern can find the solution more easily?

Regards,
Yuliana Gu

[nickchobotar]

Hello@QQQ

Your formula is correct and it should work.

Could you please check 2 things:

1) Is your Calendar table marked as Date Table (Design Tab in Power Pivot)

2) All the dates in your Calendar table must be consecutive dates, no gaps.

N -

[QQQ]

Yes, both conditions are true

[TomMartens]

Hey,

I'm wondering if the column comes from your fact table and not from Calendar table, if this is the case get your date column from your Calendar table.

Your formel is fine, here is an example you may want to look at, have a look at the report page "YTD Startert" the measure "Amount TotylYtd"

Amount TotalYtd = 
TOTALYTD(SUM('FactWithDates'[Amount]),'Calendar'[Date])

looks pretty much the same than your measure.

Cheers

[Interkoubess]

Hi @QQQ,

Please try YTD_Matches = CALCULATE(sum(Complete_Detailed_Incidents[Matches]),DATESYTD('Calendar'[date]))

And let us know.

Thx.

[QQQ]

Thanks for the quick response!

However I found the solution.

The Months should be from Date Table (which I named 'Calendar'). Instead, I originally got the dates from the Fact table.