TOTALYTD DAX not summing as a Calculated Column, only as a Measure

[Anonymous]

I'm trying to create a running total for the year, but the DAX formula TOTALYTD does not appear to be functioning correctly for me. I have this formula as a calculated column

Running YTD = TOTALYTD(SUM(Budget[Estimated Revenue]),Budget[Month])

and get this output table:

The expected result is:

From what I can find, this DAX expression should function but I only get the same value in my [Running YTD] column as the [Estimated Revenue] even with different DAX expressions which should also calculate the running ytd total

For example, I have also tried the same thing using this equation which should be identical:

VAR _Month=

     MAX('Budget'[Month])

Return

    CALCULATE(

        SUM(Budget[Volume Estimate]),

        Budget[Month] <= _Month

) 

Another thing I have tried is to use a separate date associated to the [Month] column of this table for the second value of TOTALYTD, but that also gives the same incorrect result.

UPDATE: running the CALCULATE or TOTALYTD functions works as a measure, but not as a calculated column. I am trying to create a calculated column to use DAX expressions with Power Pivot in Excel as my end goal, so a calculated column will be more beneficial to me

UPDATE 2: I created a measure `Total YTD = TOTALYTD(SUM(Budget[Estimated Revenue]),'Date'[Date])` in Power Bi as well as Excel. The Power Bi measure works as intended, but the Excel one does not sum for some reason. I can ask in a different place for Excel problems if there is a better place for this question, but I don't expect there to be much DAX support outside of Power Bi adjacent places

Power Bi:

Excel:

UPDATE 3: Using the correct date field is important on Excel as it is on Power Bi. If anyone finds this and is having a similar issue, make sure you are using the 'Calendar'[Date] and not 'Table'[Date] if you are using 'Calendar'[Date] for the TOTALYTD or similar expression

[Ashish_Mathur · Accepted solution]

Hi,

The reason you have shared to write the formula as a calculated column formula does not hold water.  Your calculation should definitely be a measure.

[Anonymous]

Thanks for looking at this, I will see if applying this DAX statement as a measure with Power Pivot will help. I am working on this data in Excel, not Power Bi, but asked here because this is the only place I know where I could ask a DAX specific question.

As a followup, is there some additional reading I can look at for why this doesn't work as a calculated column and only works as a measure?

I was intending on adding filters using EARLIER() once I got the TOTALYTD() calculating correctly, but if using a measure is benificial to what I'm working on I can always work in filter context instead.