Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate balance

I have 4 tables(Charges, payments,penalties, and adjustments) that I combined into 1 large table using power query in Power BI. The new table has 3 columns: Description, Transaction date and Amount. I would like to create a new column (Balance).

Balance for the first charge on 7/1/2010 equal the charge Amount 226.87

Next Balance(Payment)= 0.00 made on 7/21/2010 (Charge Amount[226.87]+Payment Amount[-228.87])

The next items balance will the actual balance +the next amount.
Here is a Screenshoot of the report I would like to reproduce.

Thank you in advance for your help

 

 

Need Help
  • danextian's avatar
    danextian
    2 years ago

    hi Anonymous 

     

    Try this as a calculated column:

    CALCULATE (
    SUM ( 'table'[Amount] ),
    FILTER (
        ALL ( 'table' ),
        'table'[Date] <= EARLIER ( 'table'[Date] )
            && 'table'[customer code] = EARLIER ( 'table'[customer code] )
    )
)

     

    This as a measure assuming you a have separate dates/calendar table that has a one to many relationship to your fact table.

    CALCULATE (
    SUM ( 'table'[Amount] ),
    FILTER (
        ALL ( 'datesTable' ),
        'datesTable'[Date] <= MAX ( 'datesTable'[Date] )
    )
)

    You can cretae one using the CALENDAR function in DAX.

     

    The formulas above aside,  I would keep those four tables separate instead of combining them and just use dimension tables and relationships to bridge them all. I can imagine how slow the refresh will eventually be if it isn't slow yet.

  • danextian's avatar
    danextian
    2 years ago

    Did you use a separate dates table? If so, did you use the column from the dates table?  Not using a separate dates table in time intelligence calculations can cause ndesirable results.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Thank you very much danextian . I was able to solve my problem based on the DAX and suggestions you provided.
    I created the first measure: 

    Accumulation by date = CALCULATE(
        SUM('vw_trans_charges'[Amount]),
        FILTER(
            ALLEXCEPT('vw_trans_charges', 'vw_trans_charges'[AccountNumber]),
            'vw_trans_charges'[TransactionDate] <= MAX('vw_trans_charges'[TransactionDate])
        )
    )
    and used it to create my final measure:
    Running Accumulation = CALCULATE(
        [Accumulation by date],
        FILTER(
            'vw_trans_charges',
            'vw_trans_charges'[TransactionDate] >= MIN('Calendar'[Date]) &&
            'vw_trans_charges'[TransactionDate] <= MAX('Calendar'[Date])
        )
    ).

    I have one more question. Since I am pulling the data(Tables) from a SQL database, what will happened to my report when the server is shut down?
    NB: This is historical data(last transation date is 2023). I won't need to refresh the report and I used import mode.

6 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    hi Anonymous 

     

    Try this as a calculated column:

    CALCULATE (
    SUM ( 'table'[Amount] ),
    FILTER (
        ALL ( 'table' ),
        'table'[Date] <= EARLIER ( 'table'[Date] )
            && 'table'[customer code] = EARLIER ( 'table'[customer code] )
    )
)

     

    This as a measure assuming you a have separate dates/calendar table that has a one to many relationship to your fact table.

    CALCULATE (
    SUM ( 'table'[Amount] ),
    FILTER (
        ALL ( 'datesTable' ),
        'datesTable'[Date] <= MAX ( 'datesTable'[Date] )
    )
)

    You can cretae one using the CALENDAR function in DAX.

     

    The formulas above aside,  I would keep those four tables separate instead of combining them and just use dimension tables and relationships to bridge them all. I can imagine how slow the refresh will eventually be if it isn't slow yet.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you danextian for your assistance. I tried the measure but I am still stuck.
      I created the measure and add it to my table. I added Transaction Date, the amount, the Measure(Cummulative Amount) and it worked untill I added the description column on the table or when I apply a filter(Account number). 
      For this report I will need to filter the table by the Customer Number.
      Thank you

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago

        Did you use a separate dates table? If so, did you use the column from the dates table?  Not using a separate dates table in time intelligence calculations can cause ndesirable results.

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

    Nothing can be understood from that very small image.  Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.