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 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
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.

     

6 Replies

  • 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

      The Chart has this error message:

       

       

       

       

       

    • 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.

         

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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.