Forum Discussion

vsslasd1's avatar
vsslasd1
Helper III
3 years ago
Solved

Running Sum Question

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 thos...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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's avatar
vsslasd1
Helper III
3 years ago

Thank you and Yes, Here is some sample data:


Transaction_TypeOverrideDateAmountRunningSum

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 Receipt2/4/2022       9,100              17,453
Cash Receipt2/24/2022          250              17,703
Cash Receipt3/1/2022       4,106              21,809
Cash Receipt3/9/2022       7,413              29,222
Cash Receipt4/25/2022          921              30,143

Total Billed

1/31/2022       2,500                2,500

Total Billed

2/16/2022          410                2,910
Total Billed3/14/2022       8,700              11,610
Total Billed4/19/2022          924              12,534

Revenue Earned

1/5/2022          680                   680

Revenue Earned

3/15/2022       2,594                3,274
Revenue Earned4/25/2022       1,428                4,702

Revised Contract

1/1/2022       2,400                2,400

Revised Contract

2/16/2022          689                3,089
Revised Contract4/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 + COs4/1/2022            24              14,774
 Original Estimate + COs6/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's avatar
v-zhangti
Community Support
3 years ago

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's avatar
    vsslasd1
    Helper III
    3 years ago

    Thank you!!!