Running Sum Question

[vsslasd1]

I am trying to create a running sum formula for a graph. 

The X-Axis should have the Date

The Y-Axis should have the Running Sum Amount

The Legend should be the transaction type, and those transaction types inlcude: 

"Cost"

"Cash Receipt"

"Total Billed"

"Revenue Earned"

"Revised Contract"

 "Original Estimate + COs"

This is my formula for the running sum,  but it isn't working correctly. 

How should this formula be written?

Running SUM =

CALCULATE(

    SWITCH(SELECTEDVALUE('vwPBI_ProjectCosts'[Transaction_Type]),

        "Cost", SUM([Amounts]),

        "Cash Receipt", SUM([Amounts]),

        "Total Billed", SUM([Amounts]),

        "Revenue Earned", SUM([Amounts]),

        "Revised Contract", SUM([Amounts]),

        "Original Estimate + COs", SUM([Amounts]),

    FILTER(

        ALLSELECTED([OverrideDate]),

        ISONORAFTER([OverrideDate], MAX(OverrideDate), DESC)

    )

))

Thank you

[v-zhangti]

Hi, @vsslasd1 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

when you say it isn't working, can you explain what is not working?

also are you able to provide any sample data?

can you explain what you are trying to do with the date?  

[vsslasd1]

Thank you and Yes, Here is some sample data:

Transaction_Type	OverrideDate	Amount	RunningSum
Cost	1/15/2022	1,250	1,250
Cost	2/7/2022	358	1,608
Cost	3/20/2022	429	2,037
Cost	4/1/2022	5,891	7,928
Cash Receipt	1/2/2022	6,500	6,500
Cash Receipt	1/19/2022	1,853	8,353
Cash Receipt	2/4/2022	9,100	17,453
Cash Receipt	2/24/2022	250	17,703
Cash Receipt	3/1/2022	4,106	21,809
Cash Receipt	3/9/2022	7,413	29,222
Cash Receipt	4/25/2022	921	30,143
Total Billed	1/31/2022	2,500	2,500
Total Billed	2/16/2022	410	2,910
Total Billed	3/14/2022	8,700	11,610
Total Billed	4/19/2022	924	12,534
Revenue Earned	1/5/2022	680	680
Revenue Earned	3/15/2022	2,594	3,274
Revenue Earned	4/25/2022	1,428	4,702
Revised Contract	1/1/2022	2,400	2,400
Revised Contract	2/16/2022	689	3,089
Revised Contract	4/2/2022	2,140	5,229
Original Estimate + COs	2/15/2022	8,250	8,250
Original Estimate + COs	3/31/2022	6,500	14,750
Original Estimate + COs	4/1/2022	24	14,774
Original Estimate + COs	6/1/2022	1,700	16,474

And the chart is here, but this does not correlate to the data:

X Data Point is the Overridedate column, Running sum and transaction type= ChartTranType

[v-zhangti · Accepted solution]

Hi, @vsslasd1 

You can try the following methods.
Column:

Column = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        'Table',
        [OverrideDate] <= EARLIER ( 'Table'[OverrideDate] )
            && [Transaction_Type] = EARLIER ( 'Table'[Transaction_Type] )
    )
)

Measure:

Running sum = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        [OverrideDate] <= SELECTEDVALUE ( 'Table'[OverrideDate] )
            && [Transaction_Type] = SELECTEDVALUE ( 'Table'[Transaction_Type] )
    )
)

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vsslasd1]

Thank you!!! 

[vsslasd1]

The Chart has this error message: