Forum Discussion

Yoshimitsu411's avatar
Yoshimitsu411
Resolver I
4 years ago

TotalYTD Based on Sum is Showing Incorrect Values

Hi All

 

I have the following measures:

 

UseRelationship is commented out because I didn't think it was necessary given the Quantities table is connected to the Date table indirectly via other tables.

RE Sum = 
CALCULATE(
    SUM('QUANTITIES'[RE Calculated])
    //   USERELATIONSHIP(
    //       'QUANTITIES'[Finish Date],
    //      'Calendar'[Date]
    //  )
)

 

My YTD measure is based on the measure above:

 

RE YTD = 
CALCULATE(
    TOTALYTD(
        [RE Sum],
        'Calendar'[Date]
    )    
)

 

I end up with values that are inconsistent for Feb onwards. Any idea why this is happening when the TotalYTD is based on the Sum measure?

 

 

Thanks

 

Yoshi

Need Help

5 Replies

    • Yoshimitsu411's avatar
      Yoshimitsu411
      Resolver I
      4 years ago

      Thanks amitchandak 

       

      Calendar table is already marked as a Date table. I cannot create an active relationship to it from my table due to other tables being present (circular dependency).

       

      I think because there is no active relationship because my fact table and the Calendar table it is picking up values for other months. I need to now how to only show values for dates that are valid in the fact table and Calendar table.

       

      Thanks.

  • Yoshimitsu411's avatar
    Yoshimitsu411
    Resolver I
    4 years ago

    I have made some progress but still cannot work out why YTD for March is not blank when the sum is 0.00??

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Yoshimitsu411 ,

       

      It's hard to reproduce the scenario, can you share some sample data so that we could check the formula?

       

      Best Regards,

      Jay

      • Yoshimitsu411's avatar
        Yoshimitsu411
        Resolver I
        4 years ago

        Hi Anonymous 

         

        In the screenshot below, I want to be create a running total by month without using TotalYTD or DatesYTD. 

        TotalYTD and DatesYTD do no work due to the inactive relationship between my Fact table and Calendar table. So I need another way to sum the current month + previous month totals for each month.

         

        This is my measure for RE column

        RE = 
CALCULATE(
    SUM('Fact Table'[RE]),
      USERELATIONSHIP(
          'Fact Table'[Finish Date],
         'Calendar'[Date]
     )
)

         

         

        The numbers I am hoping to see for RE Month Running Total are 

         

        JAN - 1823.93
        FEB - 3924.38
        MAR - 6854.58
        APR - 9485.38
        MAY - 16197.27
        JUN - 22117.43
        JUL - 29069.76
        AUG - 34472.28
        SEP - 35423.93
        OCT
        NOV
        DEC

         

        Thank you.

         

        Yoshi