How do I calculate "Sell Through Rate" using a measure

[l0ganBI]

New user here. I am trying to develop a measure that will calculate the January sell through rate by individual product using data from three different tables.

I have created a one to many relationship between the three tables through the product name.

Sell through % = [units sold in january] / ( [beginning of January inventory] + [units produced January] )

Here are some example tables:

Units Sold Table

Product Name	Units	Sale Date
Apples	1200	1/5/20
Oranges	400	1/3/20
Bananas	600	2/15/20
Grapes	500	2/10/20

Beginning of month inventory (BOM Table)

Product Name	Units	BOM Date
Apples	800	1/1/20
Oranges	1000	1/1/20
Bananas	300	1/1/20
Grapes	3000	1/1/20

Units produced table

Product Name	Units	Production Date
Apples	1000	1/5/20
Oranges	500	1/10/20
Bananas	500	1/15/20
Grapes	1000	2/5/20

Some help on this would be greatly appreciated! Thank you.

[v-easonf-msft]

Hi , @l0ganBI 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or your reply as Answered to close this thread?

Best Regards,
Community Support Team _ Eason

[l0ganBI]

Answered

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Devesh790]

Hi Ashish,
In your PBIX file the closing balance for January month is coming -200

[Ashish_Mathur]

That is an old post.  if you need my help, share some data, describe the question and show the expected result.

[Devesh790]

Hi Ashish,

I have downloaded your PBIX file , and checked how you have calculated. 
In your Pbix file the closing is wrongly calculated or its showing the incorrect values.

This is the screenshot from your PBIX file.

[Devesh790]

Hi,
I have resolved this issue by changing 

CB = CALCULATE(SUM('BOM Inventory'[Units]),DATESYTD('Calendar'[Date],"31/12"))+CALCULATE(Production[Produced]-'Units sold'[Sold],DATESYTD('Calendar'[Date],"31/12"))

What if there is no Inventory table is present and all the calculation has to be done from production table.

[Ashish_Mathur]

How will the opening stock be calculated then?

[Ashish_Mathur]

Hi,

The data there is of 2020.  In the Closing stock measure, the function used is TODAY().  Use this measure and all will work well

Closing balance = CALCULATE(SUM('BOM Inventory'[Units]),DATESBETWEEN('Calendar'[Date],DATE(2020,1,1),DATE(2020,1,1)))+CALCULATE([Produced]-[Sold],DATESYTD('Calendar'[Date],"31/12"))

[v-easonf-msft]

Hi , @Ashish_Mathur  @l0ganBI 

The solution from @Ashish_Mathur  looks helpful to you .

But the  denominator seems  to be wrong.

I think the measure should be modified as below:

Sell through rate (%) = [Sold]/([Produced]+[Opening balance])

Best Regards,
Community Support Team _ Eason

[amitchandak]

Join all three table to date calendar. with their respective dates

BOM Inv = Calculate(sum(BOM[Units]), Filter(('Date'),'Date'[Date]= startofmonth('Date'[Date])))					
 
MTD Sales = CALCULATE(SUM('produced table'[Units]),DATESMTD('Date'[Date]))

MTD Sales = CALCULATE(SUM('produced table'[Units]),DATESMTD('Date'[Date]))

If you select month year in the slicer , you can use the simple sum for last 2

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin