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])))

amitchandak's avatar
amitchandak
Super User
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])))

  • Meghanshu's avatar
    Meghanshu
    Frequent Visitor
    6 years ago

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

    • 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!

         

         

  • Meghanshu's avatar
    Meghanshu
    Frequent Visitor
    6 years ago

    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 !!