cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Febin
Frequent Visitor

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:

 

Febin_0-1664097726844.png

 

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):

 

Febin_1-1664098274428.png

 

Please help me in figuring this out.

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
PaulDBrown
Super User
Super User

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
        )
    )

calc column.png

 

As a measure

the model:

model.png

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

measure.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Super User
Super User

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
        )
    )

calc column.png

 

As a measure

the model:

model.png

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

measure.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi, Thank you very much!!

  • It worked!
PaulDBrown
Super User
Super User

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.