Calculate Inventory Roll Forward to future dates

[sprakash1192]

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.

[lbendlin]

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.

[Ashish_Mathur]

Hi,

Please share a sample dataset and show the expected result.

[amitchandak]

@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-Y...


Appreciate your Kudos.

[sprakash1192]

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)

    )

)

[sprakash1192]

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

[lbendlin]

How would a running total work for inventory numbers?