Forum Discussion

paolomint's avatar
paolomint
Icon for Helper III rankHelper III
5 years ago
Solved

Rolling sum not from zero

Hi, I'm workin on a running sum but this time I need something different: As first step I built  a standard rolling sum:   ROLLING SUM  = CALCULATE(sum(SCADENZE[IMPORTO]), filter(ALLSELECTED(SCAD...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi paolomint ,

     

    Maybe I understand what you mean.Based on your description, you have two tables, and you want the amount retained in the "bank" table as the starting amount of the "flow" table.

    Try the measure.

    ROLL_FLOW =
VAR base = [ROLL_BANK]
VAR end_date =
    CALCULATE ( MAX ( Bank[Date] ), ALL ( Bank ) )
RETURN
    IF (
        MAX ( FLOW[Date] ) > end_date,
        CALCULATE (
            SUM ( FLOW[VARIATION] ),
            FILTER ( ALLSELECTED ( FLOW ), FLOW[Date] <= MAX ( FLOW[Date] ) )
        ) + base,
        BLANK ()
    )

    Sample .pbix 

     

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

paolomint's avatar
paolomint
Icon for Helper III rankHelper III
5 years ago

Hello @Fowmy

thank you for your quick response, it doesn't work,

Let me try to explain better with an example

Account balance transactions are in a table [BANK]

OPENING BALANCE: 1000

DateVariation
01.09.2020+300
02.09.2020-400
05.09.2020+800

BALANCE FINAL : 1700

To get the final balance I can use a classic RUNNING SUM formula:

ROLL_BANK: CALCULATE(sum(BANK[VARIATION]), filter(ALLSELECTED(BANK), BANK[DATE]<-MAX(BANK[DATE])))

--------------------------------------------------------------------------------------------------------------------------------------

In another table I have the future varation [FLOW]

DateVariation
25.09.2020+200
30.09.2020-500
05.10.2020- 400
ROLLING SUM- 700

RUNNING SUM Formula:

ROLL_FLOW: CALCULATE(sum(FLOW[VARIATION]), filter(ALLSELECTED(FLOW), FLOW[DATE]<-MAX(FLOW[DATE])))

---------------------------------------------------------------------------------------------------------------------------------------

I need a measure to get the following result:

DateVariation
25.09.20201900
30.09.20201400
05.10.20201000

Thank you for your help

Good bye

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Hi,

In the BANK Table, it does not make sense to have a text entry (Opening balance) in a Date column.  There should be a Date in place of that text entry.

  • paolomint's avatar
    paolomint
    Icon for Helper III rankHelper III
    5 years ago

    Ashish_Mathur

    thanks for your reply, 

    it was obvius, anyhow to avoid any misunerstanding, I modify the post.

    Now the data are showed in the correct way, can you help me?

    Thank you very much

    Paolo 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      Hi,

      I still do not understand.  The Opening Balance has to as of a certain date.  So it should be a part of Table1 (first row) with a date assigned to 1000.

    • V-lianl-msft's avatar
      V-lianl-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi paolomint ,

       

      Maybe I understand what you mean.Based on your description, you have two tables, and you want the amount retained in the "bank" table as the starting amount of the "flow" table.

      Try the measure.

      ROLL_FLOW =
VAR base = [ROLL_BANK]
VAR end_date =
    CALCULATE ( MAX ( Bank[Date] ), ALL ( Bank ) )
RETURN
    IF (
        MAX ( FLOW[Date] ) > end_date,
        CALCULATE (
            SUM ( FLOW[VARIATION] ),
            FILTER ( ALLSELECTED ( FLOW ), FLOW[Date] <= MAX ( FLOW[Date] ) )
        ) + base,
        BLANK ()
    )

      Sample .pbix 

       

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