cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Advocate I

## TOTALYTD does NOT take cumulative

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate I

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

7 REPLIES 7
Highlighted
Solution Sage

Hi @QQQ,

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

And let us know.

Thx.

Highlighted
Super User II

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Continued Contributor

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 -

Highlighted
Advocate I

Yes, both conditions are true

Highlighted
Advocate I

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.

Highlighted
Advocate I

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

Highlighted
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

## Helpful resources

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors