DAX Query for opening balance

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!