Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
This is the result I want
But this is what i get
My model
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
Solved! Go to Solution.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |