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