Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
cyuan
Frequent Visitor

Rolling balance of inventory projection

Hi all,

 

I am working on a measure to present the closing balance of inventory on SKU level

basica logic is SOH-residual balance of forecast+inbounds on monthly basis:

 

The DAX i had give me below result

ATP =
var _Inbounds=calculate([Inbounds],filter(all('01 Calendar'[Date]),'01 Calendar'[Date]<=MAX('01 Calendar'[Date])))
var _FC=calculate([Current FC],filter(all('01 Calendar'[Date]),'01 Calendar'[Date]<=MAX('01 Calendar'[Date])))
return
calculate([SOH_FULL]-IF(_FC>=[Actual],_FC-[Actual],0)+_Inbounds,filter('01 Calendar',min('01 Calendar'[Date])>=DATE(YEAR(TODAY()),1,1)))

cyuan_0-1726460390060.png

 

It should be 

 SOH_FullInboundsAactualCurrent FCATPFormula
Sep-248331 110719907448=B2+C2+IF(E2<D2,0,E2-D2)
Oct-24   15605888=F3-E2+C2
Nov-24   18834005=F3-E4+C4
Dec-24   23371668=F4-E5+C5
Jan-25   1567101 
Feb-25 6000 13714730 
Mar-25   20102720 
Apr-25   2083637 
May-25   1707-1070 
Jun-25   1675-2745 
Jul-25   1608-4353 
Aug-25   2334-6687 
Sep-25   2062-8749 

 

In addition, as above is on SKU level, I also need a view of product family level which means sum up all postive numbers/zero out all negative month over month.

 

Highly appreciate if your help in advance.

 

Chen

2 REPLIES 2
DataNinja777
Super User
Super User

Hi @cyuan ,

 

My concern with summing only the positive values and zeroing out the negative ones is that it will consistently overstate the inventory in the model. 

Best regards,

Hi ninja,

thanks reply. in my case forecast will expire month over month, hence no backorder qty should be counted in, once inventory balance is negative or zero, new month forecast should also be zero out too because nothing we could supply such that inventory will not be inflated. 

 

hope it makes sense.

 

CY

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors