Forum Discussion

Meghanshu's avatar
Meghanshu
Frequent Visitor
6 years ago
Solved

DAX Query for opening balance

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Meghanshu , the opening should be 1 less?

     

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

richbenmintz's avatar
richbenmintz
Resident Rockstar
6 years ago

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's avatar
richbenmintz
Resident Rockstar
6 years ago

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!