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