I tried reviewing open forum topics and did not find any that are relevant. If there is an existing post that covers this please refer me to it
I work in purchasing and I am fairly new to using Power BI. Since I am so new, I have found it simpler to write several measures rather than one large measure. I am working on a project that calculates a company term called " Open to Buy" and “To Purchase” I getting to this by writing the following measures:
Demand QTY=CALCULATE(SUM(‘Demand Forecast’[Sales Forecast]))`
Cumulative Demand =CALCULATE( [Demand QTY], FILTER(ALLSELECTED(‘Date Table’), ‘Date Table’[Date] <=MAX(‘Date Table’[Date])))
Open to Buy =CALCULATE([Cumulative Demand]-‘Inventory Position’[Sum of Qty on Hand (Primary UOM)]-‘Inventory Position’[Sum of Qty on Open PO (Primary UOM)])
All fairly simple so far. However, this is returning values that I can not purchase on. I have to purchase products in case quantities and I have to purchase many products at once all with potentially different case quantities. I have an item table as a look up table that is related to my sales, demand, and inventory tables. My item table has my case quantity values for all of my items
The next step i tried is below, but it is not working and i am not sure what to do next…My end goal is to transform my open to buy value to an even case quantity that is the next lower increment.
Example: Open to buy value = 17 , case qty for that product is 5, open to buy value of 17 is transformed to a “to purchase” value of 15.
To purchase =IF([Open to Buy]<=0,0, FLOOR([Open to Buy], (‘Item Info’[CASE QTY (PUOM)])))
Thanks in advance for any assistance.