Rolling on hand total

[Anonymous]

Hello Community: 

I have a date table.  

Date table is connected to an on-hand inventory table and also a demand table (outstanding requirement).

The on-hand qty is always a singular point in time based on the date the report was ran (today in this case).   The demand qty goes out several months.    What I need is for the current on hand qty to be deducted (based on demand) from the future months.  

So, to simplify:   1,913 is the current on-hand qty.   We have a demand this month of 114.   A surplus of 1,799.       The March on-hand qty should basically equal the remaining surplus of the prior month.   So in this case, March on-hand needs to say 1,799.  

The March Surplus column would also then need to update accordingly.   So it should say 1,479.  

Thanks in advance!

[amitchandak]

Make sure you have date table, then try

Cumm Sales = CALCULATE(SUM(table[On hand Qty]),filter(date,date[date] <=maxx(date,date[date])))
-CALCULATE([sum outstanding requirement],filter(date,date[date] <=maxx(date,date[date])))

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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Hi Amit, 

That works just to get the surplus amount.   But what I'm trying to do is to have a rolling sum.    

So, in February the demand is 889 units which will deplete the starting Feb balance of 1660 down to 771.   This 771 is the Surplus for the end of Feb, but also needs to be the starting On Hand qty for March...and so on.  

[amitchandak]

Share some sample data. This needs to done using Cumulative and initial stock. or previous month measure. But that can only be done it last month had hard numbers likes sales.

[Anonymous]

I was able to get to partially work but using this measure:

Previous Month On Hand = CALCULATE(SUM('Items (3)'[Quantity On Hand])-[Outstand Req],USERELATIONSHIP('Date Table'[Date],Items[Last Gen Date]), PREVIOUSMONTH('Date Table'[Date]))

The only challenge now is I need to constrain the dates to the last-most date from my Outsanding Requirement field. In this case July. But the visual is also showing the remaining months. (Aug - Dec).

Outstand Req = CALCULATE(SUM(Flu_PlanPegging[Outstanding Requirement]),filter('Date Table','Date Table'[Date] <=maxx('Date Table','Date Table'[Date])))