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.

     

vanessafvg's avatar
vanessafvg
Community Champion
3 years ago

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'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!!!