Forum Discussion

QQQ's avatar
QQQ
Icon for Advocate II rankAdvocate II
9 years ago
Solved

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
Need Help
  • QQQ's avatar
    QQQ
    9 years ago

    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

     

     

     

     

7 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
    Interkoubess
    Icon for Solution Sage rankSolution Sage
    9 years ago

    Hi QQQ,

     

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

     

    And let us know.

     

    Thx.

     

     

     

    • QQQ's avatar
      QQQ
      Icon for Advocate II rankAdvocate II
      9 years ago

       

      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.

  • nickchobotar's avatar
    nickchobotar
    Icon for Skilled Sharer rankSkilled Sharer
    9 years ago

    HelloQQQ

     

    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's avatar
      QQQ
      Icon for Advocate II rankAdvocate II
      9 years ago

      Yes, both conditions are true

  • QQQ's avatar
    QQQ
    Icon for Advocate II rankAdvocate II
    9 years ago

    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's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      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