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
