Keep Opening Balance for first month in filter and ignore the other months. Keep Transactions

[Anonymous]

Item	Amount	Feb	Mar	Apr
Op Balance	1000	1100	1203	1309.09
Transactions	100	103	106.09	109.2727

Hi Everyone,

I have the above data. I am trying to build a balance sheet. I am trying to keep it dynamic. I am hoping to create  a calculated column that has opening balance of first month in filter and sum of transactions in those months

So for example, If I select March and April balance sheet, it would keep March opening balance and sum of both months transactions.

Is it possible?

[danextian · Accepted solution]

You can create measures like these:

Beginning Balance =
CALCULATE (
    SUM ( Journals[Amount] ),
    FILTER ( ALL ( Dates ), Dates[Date] < MIN ( Dates[Date] ) )
)

Ending Balance =
[Beginning Balance] + SUM ( Journals[Amount] )

[Anonymous]

@danextian @amitchandak  Thank you your solution works great.  Now my next challenge is that i need to filter based on another table's field called fund name in fund table. I googled and tried to add another all except but i need to still only fetch the first date which is in date table. When i add the 'relational COA_Transactions'[Trx_Date] < MIN ( 'relational COA_Transactions'[Trx_Date])) in the second all except it gives an error. Would you kindly have a solution to this?

My Formula:

Beginning Balance =

CALCULATE (

SUM ( 'relational COA_Transactions'[Trx_Amount_Adj] ),

FILTER ( ALLEXCEPT(('relational COA_Transactions'),'relational COA_Transactions'[COA L1 Code], 

'relational COA_Transactions'[Trx_Date] < MIN ( 'relational COA_Transactions'[Trx_Date]))

)

[amitchandak]

You need to build for date <= till date

Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601