Calculate Month of Supply, based on latest sell out

[Anonymous]

Hi, 

I would really appreciate your help with the below question. I am very new to Power BI and I can't seem to find a solution for this anywhere. 

I am looking to Calculate the Month of Supply=Latest week stock/ sum of last 4 weeks of Sales. 

Below is an example of Raw data I have, in this case, I would like to use Wk 29 stock (57)/ Sum of Week 26,27,28,29 Sales

This data will be updated every week, on Wk 30, the formula should be = Wk30 stock/sum of week 27,28,29,30 sales

Distributor/Retailer	Year	Wk	Posting Date	Razer Pdt code	Item/Service Description	Sales	stock
Customer 1	2021	27	7/11/2021	2043	Product A	6	50
Customer 1	2021	27	7/11/2021	2043	Product A	28	68
Customer 1	2021	28	7/18/2021	2043	Product A	2	47
Customer 1	2021	28	7/18/2021	2043	Product A	1	72
Customer 1	2021	29	7/25/2021	2043	Product A	9	57

Thanks in advance 

[v-angzheng-msft]

Hi, @Anonymous 

Try to create a measure like this:

_MonthOfSupply = 
var _last4W=
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(ALL('Table'),'Table'[Wk]>=MAX('Table'[Wk])-4&&'Table'[Wk]<MAX('Table'[Wk])))
var _stock=MAX('Table'[stock])
return DIVIDE(_stock,_last4W)

Result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a date table and there create week related columns 

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Stock'[stock]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

Last 4 weeks sales= CALCULATE(sum('Table'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Ratio = divide([This week], [Last 4 weeks sales])

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

Hi @amitchandak 

Thanks for your help I managed to calculate the Month of Supply. I am now trying to display my matrix as a separate table from the main table. do you have any idea how i can do that?

you can see the below photo for your reference, rather than the Month of supply showing under Week, it should show 1 column as of to date