Month of Sales calculation

marco_cers — Wed, 18 Jan 2023 18:20:35 GMT

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

topic Month of Sales calculation in DAX Commands and Tips

Month of Sales calculation