Month of Sales calculation
Hello everyone, I'm trying to find my way in solving the below request, any support is really appreciated! What I'm trying to do is set up a Measure on Power BI that calculates the Stock coverage. The general formula is the following: MoS = Stock measure / (Average Sales in Month N+1 / N+2 / N+3) The Denominator is what I can't get my head around because depending on the forecast cycle I choose and the availability of data I have, I need to tell Power BI what to consider as 3 Months sales. 2 examples below may help explain my doubts further: 1. 2022 December actuals will now need to consider Jan, Feb, Mar 2023 as Sales for the Denominator, based on the forecast that was submitted now in January. Next month I still need 2022 December MoS to take Jan, Feb and Mar Sales, but I need to recalculate based on a different Cycle (the one we will do in February that has January actuals). So I need the formula to be dynamic 2. I only have forecasts up to 2023 so as I approach the end of the year I need to take into consideration the last 3 months available (so October/November/December MoS will take the last 3 months' sales as denominator rather than the N+1/N+2/N+3) I tried approaching the problem above with a supporting table to try and set out the rules. As you can see below, at a given cycle, month and year, I should be able to establish the corresponding cycle, month and year of the Sales I need at the denominator: Cycle Month Year Key Month 1 Month 2 Month 3 PO_01_2023 Jan 2023 PO_01_2023Jan2023 PO_01_2023Feb2023 PO_01_2023Mar2023 PO_01_2023Apr2023 PO_01_2023 Feb 2023 PO_01_2023Feb2023 PO_01_2023Mar2023 PO_01_2023Apr2023 PO_01_2023May2023 PO_01_2023 Mar 2023 PO_01_2023Mar2023 PO_01_2023Apr2023 PO_01_2023May2023 PO_01_2023Jun2023 PO_01_2023 Apr 2023 PO_01_2023Apr2023 PO_01_2023May2023 PO_01_2023Jun2023 PO_01_2023Jul2023 PO_01_2023 May 2023 PO_01_2023May2023 PO_01_2023Jun2023 PO_01_2023Jul2023 PO_01_2023Aug2023 PO_01_2023 Jun 2023 PO_01_2023Jun2023 PO_01_2023Jul2023 PO_01_2023Aug2023 PO_01_2023Sep2023 PO_01_2023 Jul 2023 PO_01_2023Jul2023 PO_01_2023Aug2023 PO_01_2023Sep2023 PO_01_2023Oct2023 PO_01_2023 Aug 2023 PO_01_2023Aug2023 PO_01_2023Sep2023 PO_01_2023Oct2023 PO_01_2023Nov2023 PO_01_2023 Sep 2023 PO_01_2023Sep2023 PO_01_2023Oct2023 PO_01_2023Nov2023 PO_01_2023Dec2023 PO_01_2023 Oct 2023 PO_01_2023Oct2023 PO_01_2023Oct2023 PO_01_2023Nov2023 PO_01_2023Dec2023 PO_01_2023 Nov 2023 PO_01_2023Nov2023 PO_01_2023Oct2023 PO_01_2023Nov2023 PO_01_2023Dec2023 Thanks again for your support! Best, Marco
Calculate future cumulative stock with missing dates
Hi, I want to calculate the cumulative stock per week based on the current stock and the planned consumption in the upcoming weeks and put it in a matrix. The measure i'm using is: cumulative stock = calculate([Current stock] - 'Planned'[Planned consumption],FILTER('Date','Date'[Date] >= today())) Where "planned consumption" is a measure of the sum of the planned quantity in table "Planned". In some of the weeks there are no orders planned so in my planned consumption table there are dates/weeks missing. I'm using a date table which is linked to the planned consumption tabel based on the planned date. Example of values in table "Planned" The current outcome of my measure is: The numbers in green are my desired results. What do I need to adjust to make this work? Thanks in advance! 🙂
How to calculate stock rotation index through movements?
Hi, I'm new to the forum and I'm Italian so sorry for the bad English. However, I want to calculate the stock rotation index for the items of my warehouse. The available tables are: FACT_STOCK: the current stock for the items in each warehouse is periodically measured and registered here STK_ID STK_ITM_ID STK_WHS_ID STK_QTY STK_DATE 1 I0001 WH1 40 1 Jan 18 2 I0001 WH1 40 2 March 18 3 I0001 WH1 37 5 June 18 4 I0002 WH2 30 2 March 18 The table does not contain the stock quantity of each item. Some of them are missing. FACT_MOV: contains all the movements (sales, transfers, returns) and the correspondent quantity and date STK_ID MOV_CAU MOV_ITM_ID MOV_WHS_ID MOV_QTY MOV_DATE A1 Sale I0001 WH1 12 12 Dec 17 A2 Sale I0001 WH1 24 20 Dec 17 A3 Sale I0001 WH1 3 10 April 18 A4 Return I0001 WH1 10 8 Jul 18 The update of the stock is not immediate: for example, on 2nd March 2018 the stock level of I0001 was of 40 pieces; in the next month (10th April 2018) 3 pieces were sold and in the next month (5th June 2018) the stock level was updated. MASTER_ITEM: each item belongs to a group of product and each group belongs to a specific category ITM_ID ITM_GROUP ITM_CATEGORY I0001 Wine Blue I0002 Milk Blue I0003 Beer Red How can I calculate the stock rotation index as total quantity sold divided by average stock?
