Calculate Opening, Movement and Closing Balances | PowerBI

17 Views Asked by At

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 enter image description here

But this is what i get

MichelleLash_1-1709664928120.png

My model

MichelleLash_2-1709665039247.png

Dates (Date, Year, Month)

Table (Date, Item, Qty, Category)

MichelleLash_4-1709665085813.png

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
0

There are 0 best solutions below