Create Running Total On A Calculated Measure

[dax_bee]

Hi.

I am trying to create a running total for a field that is defined as a Calulated Measure.  Having read numerous posts I am clear about the formula struture which needs to be implemented;

RunningTotal = CALCULATE(SUM(my_field),

FILTER(ALL(my_table),

my_table.date_field <= MAX(my_table.date_field)

)

)

The issue is regarding the SUM component.  "my_field" is a very complex measure and the customer would like a running total of this field year on year.  I know SUM cannot be used in conjunction with a measure, so can anyone help with how best to implement this requirement?

Thanks In Advance.

[SaraM]

Hi there,

I am using this formula to run the total in my table but it doesnt work . it bring the exact same value from the column i am looking to sum.

Column 3 is monthly cost and column 2 is dates.

Do I need to chnage the formula???

CUMULATIVE  = CALCULATE (
    SUM ( Phasing[Column3] ),
    FILTER (
        ALL ( Phasing[Column3] ),
        Phasing[Column2] <= MAX ( Phasing[Column2] )

    )
)

[SaraM]

Hi,

I am using this formula in my tables for running totals on column **bleep** from coulmn 3 with dates in coulmn 2 but it doesn't seem to work. it bring the exact value as coulmn 3 and not totals. Is there anything that i need to change ?

CUMULATIVE = CALCULATE (
    SUM ( Phasing[Column3] ),
    FILTER (
        ALL ( Phasing[Column3] ),
        Phasing[Column2] <= MAX ( Phasing[Column2] )
    )
)

[Anonymous · Accepted solution]

Hi @dax_bee,

Create running total using  the following DAX formula and check if you get desired result.

RunningTotal = SUMX(FILTER(ALLSELECTED('Calendar'[DateKey]),'Calendar'[DateKey]<=MAX('Calendar'[DateKey])),[my_field])

If the above Dax doesn’t help in your scenario, please help to share sample data of your  table and post expected result.

Thanks,
Lydia Zhang

[punitmicrosoft]

THanks a lot. I also had same query .

[fraofempire]

Hello everyone, I have to calculate cumulative sum for distinct Ben per day in order to obtain this

My table have these columns:

[arsalaankhan]

Many thanks for sharing this. Helped big time. I hope you wont mind answering one quick question? When I used this formula, the cumulative total continues to show constant value throughout the x axis even if there is zero value for the later groups. How can we stop running total from continuing all the way? Please advise. Thanks

[dax_bee]

Thank you for all your help.

v-yuezhe-msft - your solution worked a treat!

[Sunkari]

@dax_bee: What's the logic of my_field.

[dax_bee]

Hi.

The logic of my_field is very complicated and consists of references to a number of other columns.  So ideally I do not want to replicate within the Running Total logic.

[Sunkari]

Hmm. As don't the exact logic of my_field, if possible try to push that logic at ETL(Power Query) level and use that column in sum function.