Forum Discussion

rajasekaro's avatar
rajasekaro
Helper III
2 months ago
Solved

Closing value

Hi team ,  how to calculate closing value  i have account data  ACCOUNT APYC DATE AMOUNT 43060 482727800 30-04-2024 -360868242 43060 482727800 31-05-2024 -92756178 43060 4827...
  • Lodha_Jaydeep's avatar
    2 months ago

    Hi rajasekaro,
    Please have a look at the below solution.

    1) Create power Bi measures for the closing

    ClosingV3 = 
    VAR Opening = MAX(account_transactions[APYC])
    VAR CurrentDate = MAX(account_transactions[DATE])
    VAR CurrentAccount = MAX(account_transactions[ACCOUNT])
    VAR CumulativeAmount =
        CALCULATE(
            SUM(account_transactions[AMOUNT]),
            REMOVEFILTERS(account_transactions),
            account_transactions[ACCOUNT] = CurrentAccount,
            account_transactions[DATE] <= CurrentDate
        )
    RETURN
    Opening + CumulativeAmount

    OR

    Closing_v4 = 
    VAR Opening = MAX(account_transactions[APYC])
    RETURN
    Opening +
    CALCULATE(
        SUM(account_transactions[AMOUNT]),
        ALLEXCEPT(account_transactions, account_transactions[ACCOUNT]),
        account_transactions[DATE] <= MAX(account_transactions[DATE])
    )

     2) Copy that measure in the table/matrix visual

     

    Please consider as an accepted solution if worked or give some kudod.

  • ryan_mayu's avatar
    2 months ago

    rajasekaro 

    if you want to create a column ,you can try this

    Column = 'Table'[APYC]+sumx(FILTER('Table','Table'[ACCOUNT]=EARLIER('Table'[ACCOUNT])&&'Table'[DATE]<=EARLIER('Table'[DATE])),'Table'[AMOUNT])