cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
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 LabelsYTD_MatchesSum of Matches
Jan38076323807632
Feb17860601786060
Mar504310504310
Apr609969609969
May12777431277743
Jun35275793527579
Jul20413832041383
Grand Total1355467613554676
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate I
Advocate I

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

correct_table.PNG

 

 

 

 

View solution in original post

7 REPLIES 7
Highlighted
Solution Sage
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
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
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

Yes, both conditions are true

Highlighted

 

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
Advocate I

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

correct_table.PNG

 

 

 

 

View solution in original post

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors