Forum Discussion

Febin's avatar
Febin
Frequent Visitor
3 years ago
Solved

TOTALYTD Giving same value as the source column

Hi,

 

I'm trying to create a simple running total column for the values in "Distance Travelled". My fomula in the column "RunningTotl" is as follows:

 

 

I'm having a separate date table "DateDim" with only dates, years and names of month.

 

But my formula is not working. It still gives the same values as the column "Distance Travelled".

 

I have tried the same with measure and using the measure to fill the column, still no use.

 

However, the formula seems to be working fine with a test table i created myself with some random data (see below):

 

 

Please help me in figuring this out.

 

Thanks in advance!

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Ok. for the calculated column:

    Running total by Year =
VAR _Year =
    YEAR ( fTable[date] )
RETURN
    CALCULATE (
        SUM ( fTable[Value] ),
        FILTER (
            fTable,
            fTable[date] <= EARLIER ( fTable[date] )
                && YEAR ( fTable[date] ) = _Year
        )
    )

     

    As a measure

    the model:

    YTD measure = 
TOTALYTD(SUM(fTable[Value]), 'Calendar Table'[CalDate])

     

4 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Is the a reason you need these calculations as physical columns in a table? Normally these calculations are done using measures to be displayed in visuals in a report

    • Febin's avatar
      Febin
      Frequent Visitor
      3 years ago

      Not necessarily. But since I'm trying to learn it would be nice if i could have these values in the table as well.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Ok. for the calculated column:

    Running total by Year =
VAR _Year =
    YEAR ( fTable[date] )
RETURN
    CALCULATE (
        SUM ( fTable[Value] ),
        FILTER (
            fTable,
            fTable[date] <= EARLIER ( fTable[date] )
                && YEAR ( fTable[date] ) = _Year
        )
    )

     

    As a measure

    the model:

    YTD measure = 
TOTALYTD(SUM(fTable[Value]), 'Calendar Table'[CalDate])

     

    • Febin's avatar
      Febin
      Frequent Visitor
      3 years ago

      Hi, Thank you very much!!

      • It worked!