Forum Discussion
Initial / close balance doesnt seem right
Hi community,
im new to PBI, and been trying to create an Inital/Close balance, but havent been able so far, here is a little example PBIX
the basic are:
- Inital Balance (the day the report is consulted will be Day 1) will be Inventory. Then from second day on, it will be Final Balance
- Final Balance is Initial Balance - Sales of the day
This is the result I want
But this is what i get
My model
- Dates (Date, Year, Month)
- Table (Date, Item, Qty, Category)
- Table 2 (Measures for Inventory, Sale, Inital Balance, Final Balance)
Inventory = CALCULATE( SUM('Table'[Qty]), 'Table'[Category] = "inv", ALLEXCEPT(Dates, Dates[Date] ))
Sale = CALCULATE( SUM('Table'[Qty] ), 'Table'[Category] ="sale")
Initial balance =
var thisdate = MAX(Dates[Date] )
var vsales =
CALCULATE(
[Sale],
REMOVEFILTERS(Dates ),
'Table'[Date] < thisdate )
RETURN [Inventory] -vsales
Final balance =
var thisdate = MAX(Dates[Date] )
var vsales =
CALCULATE(
[Sale],
REMOVEFILTERS( Dates[Date] ),
'Table'[Date] <= thisdate )
RETURN [Inventory]- vsales
Hi MichelleLash if sale is outflow (it should be for balance logic) simply create new calculated column with if statement like on picture below and create simple measure of SUM for that new column.
SUM of column should give you balance, so slice and filter as you wish.
2 Replies
- gmsamborn
Super User
Hi MichelleLash
I made changes to [Initial Balance] and [_MyFinBal] (my Final Balance).
Initial balance = VAR thisdate = MAX( 'Table'[Date] ) VAR vsales = CALCULATE( [Inventory] + [Arrive] - [Sale], REMOVEFILTERS( 'Table'[Date] ), 'Table'[Date] < thisdate ) RETURN vsales _MyFinBal = [Initial balance] + [Inventory] + [Arrive] - [Sale]I didn't change any other measures.
Let me know if you have any questions. I hope this helps.
- some_bih
Community Champion
Hi MichelleLash if sale is outflow (it should be for balance logic) simply create new calculated column with if statement like on picture below and create simple measure of SUM for that new column.
SUM of column should give you balance, so slice and filter as you wish.
