DAX Query for opening balance

[Meghanshu]

Hi All, I have an inventory dataset having daily transactions and would like to calculate opening and closing balances.

Snapshot of inventory data is given below.

I have calculated the closing balance as per the below formula and i have separate "Date table" which has a relation with Date field from the invetory data

Closing  = CALCULATE(SUM(Table[Inventory Balance]),

FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date])))

			Calculated using Fromula	Need calculation for Opening
Date	Item	Inventory Balance	Closing	Opening
Wednesday, January 1, 2020	Laptop	1
Thursday, January 2, 2020	Laptop	3
Friday, January 3, 2020	Laptop	-2
Saturday, January 4, 2020	Laptop	3	5
Saturday, February 1, 2020	Laptop	3		5
Sunday, February 2, 2020	Laptop	-2
Monday, February 3, 2020	Laptop	4
Tuesday, February 4, 2020	Laptop	-2	8 (Sum from January)
Sunday, March 1, 2020	Laptop	5		8
Monday, March 2, 2020	Laptop	-1
Tuesday, March 3, 2020	Laptop	-1
Wednesday, March 4, 2020	Laptop	2
Thursday, March 5, 2020	Laptop	-1	12

Thanks in Advance !

[amitchandak · Accepted solution]

@Meghanshu , the opening should be 1 less?

Opening= CALCULATE(SUM(Table[Inventory Balance]),
FILTER(ALL('Date'[Date]), 'Date'[Date] < MAX('Date'[Date])))

[Meghanshu]

Hi @amitchandak  

Query works perfectly fine when I change max value to min value it give me first opening transactional value of the month.

Thanks !!

[Meghanshu]

Opening of any month is equal to closing of my previous month.

[richbenmintz]

Hi @Meghanshu,

You can try the following calculated columns

Closing Balance = 
    var curDate = 'Table'[Date]
    var _item = 'Table'[Item]
    var invMonth =  CALCULATE(max('Date'[year-month]), FILTER('Date', 'Date'[Date] = curDate))
    var maxInvDate = CALCULATE(MAX('Date'[Date]), FILTER(ALL('Date'), 'Date'[year-month] =invMonth), FILTER('Table', 'Table'[Inventory Balance]<> 0 && 'Table'[Item] = _item))
return 
    if('Table'[Date] <> maxInvDate, BLANK(), CALCULATE(sum('Table'[Inventory Balance]), FILTER(ALL('Table'), 'Table'[Date]<=maxInvDate && 'Table'[Item] = _item)))

Opening Balance = 
    var curDate = 'Table'[Date]
    var _item = 'Table'[Item]
    var invMonth =  CALCULATE(max('Date'[year-month]), FILTER('Date', 'Date'[Date] = curDate))
    var maxInvDate = CALCULATE(min('Date'[Date]), FILTER(ALL('Date'), 'Date'[year-month] =invMonth), FILTER('Table', 'Table'[Inventory Balance]<> 0 && 'Table'[Item] = _item))
return 
    if('Table'[Date] <> maxInvDate, BLANK(), CALCULATE(sum('Table'[Inventory Balance]), FILTER(ALL('Table'), 'Table'[Date]<maxInvDate && 'Table'[Item] = _item)))

One thing to note is that you will probably want to create measures that supress the total as the values of these calc columns is not additive

Hope this Helps,

Richard 

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[richbenmintz]

Hi @Meghanshu,

Question 1:

Something like below should work

_opening balance = 
    SWITCH(TRUE(), 
        ISBLANK(SUM('Table'[Inventory Balance])), BLANK(),
        HASONEVALUE('Date'[year-month]), CALCULATE(MIN('Table'[Opening Balance]), FILTER(ALL('Date'), 'Date'[year-month] = MIN('Date'[year-month]))),
        HASONEVALUE('Date'[Date]), MIN([Opening Balance]), 
    BLANK())

Question 2:

Depends on How you want to deal this balances, the formula currently will generate a balance per item

Thanks,

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Meghanshu]

Hi @richbenmintz ,

In the example i have mentioned a single category (Laptop) what if i have multiple category in Items.