Forum Discussion

sprakash1192's avatar
sprakash1192
Helper II
6 years ago

Calculate Inventory Roll Forward to future dates

I have calculated inventory for every date based on transactions for inventory items. I want to roll forward the ending inventory to future dates in the calendar. For example, if I an ending balance of an item today - 6/7/2020, I want today's balance for all the future dates for the rest of the year until new transactions are posted. Here is the measure I created

 

Total_Inv = CALCULATE(sum(Inventory_Roll[StockQty]),FILTER(all('Inventory_Roll'),Inventory_Roll[Date] <=Today()))
 

But this measure calculates sum totals as the inventory for the item, instead of calculating the sum by date.

 

Please help.

 
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    sprakash1192 , Better  use a date calendar

     

    Total_Inv = 
var _max = maxx(Inventory_Roll,Inventory_Roll[Date])
return
CALCULATE(sum(Inventory_Roll[StockQty]),FILTER(all('Date'),Date[Date] >=_max))

Total_Inv = 
var _max = maxx(Inventory_Roll,Inventory_Roll[Date])
return
CALCULATE(sum(Inventory_Roll[StockQty]),FILTER(all('Date'),Date[Date] =_max))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

    • sprakash1192's avatar
      sprakash1192
      Helper II
      6 years ago

      Thank you. This helps. But how would I do a running total by grouping on some columns?

      • lbendlin's avatar
        lbendlin
        Super User
        6 years ago

        How would a running total work for inventory numbers?

    • sprakash1192's avatar
      sprakash1192
      Helper II
      6 years ago

      I have created a running totat query which is computing the totals correctly now, but as I drill down the running totals get negative because of some negative transactions. Negative transations represent deduction in inventory. The running total should a net pf positive and negative. Also, the query has slowed down my model immensly.

       

      Total EA OH running total in Date =
      CALCULATE(
          SUM('Inv Txns by Date'[Total EA OH]),
          FILTER(
              ALLSELECTED('Calendar'[Date]),
              ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
          )
      )
  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    Here's my version:

     

    yes, as others have mentioned use a dates table , mark it, and link it appropriately. ALSO you need to use the date from the dates table as your first column in the visual, filter the visual or page with the appropriate date range, and enable "show items with no data"

     

    Your measure can be defined such (pseuo code):

    tq = 
var d= SELECTEDVALUE(Dates[date])
var t=CALCULATE(max(Inventory[Date]),filter(all(Dates),Dates[date]<=d))
return CALCULATE(sum(Inventory[Value]),filter(all(Inventory),Inventory[Date]=t))

    That will propagate the inventory value forward through any gap days, and pick the actual values again if available.