Sell through Calculation

DAX DAX Calculations
1

Hi all,

I am trying to calculate a sell through of products for a specific time period.
There are 2 sell through periods that I would like to calculate.

  1. Sell through Rate of SS 2020 = Total Sales for period divided by Nov 2019 Starting Stock
  2. Sell through Rate of AW 2020 = Total Sales for period divided by May 2020 Starting Stock

The time period for sell through rate of SS 2020 is Nov 2019 till May 2020 while the period for AW 2020 is from May 2020 till Feb 2021.

The starting stock would need to be fixed throughout the calculations for each month in time period.

The end result would be as such:

Capture
Capture778Ă—338 61.9 KB

Here is the pbix sample for reference.
Question.pbix (121.4 KB) Question2.xlsx (12.7 KB)

Thanks for the help in advance!

Regards
Hidayat

1 Like
2

The starting stock would need to be fixed throughout the calculations for each month in time period.

Can you give an example for Nov2019/ May 2020 ?

3

Hi Yatz_86,

Have a look at this workout.
First I made a data model with Purchases/Stock, Sales, (FactTables) Product and Date (LookupTables)
I assumed that stock per product can be cumulated, at least I calculated a cumulative stock per product, but in your example, it does not matter, as you have only 2 stock values for 2 products.

5 measures made, some complexity was to avoid calculations of sell-through without sales taken place in the visuals.

It is setup that it calculates Sell through Rate over periods, you could e.g. select periods of years.

3 Visuals on 3 pages, have a look at the attached PBIX file.

Learning experience:

If you fastly make 3 tables out of your 1 Excel table (which can easily be done in the Query Editor), as a source for the data model, ambiguity appears, and one to many relationships from the lookup tables to the fact tables (waterfall principle) are not possible ( learned another thing today :grin: ). Apparently the one Excel table is still detected as source for the 3 tables. To avoid this I decided to make 3 “normalized” input tables in Excel.

Let me know any comments,
Kind regards,

Sales through Stock deltaselect.pbix (118.0 KB)

3 Likes
4

Hi @deltaselect,

Love the solution! I was able to integrate it with my production model pretty quick with your approach.

Wish I could high five you right now!! :smiley: :v:t3:
I spent a few sleepless night over this business problem.

Regards
Hidayat

1 Like
5

Hi Yatz86,

I appreciate your comments ! Glad I could help you out.

Minor thing;
to avoid an error calculation for products with zero stock, I slightly adjust the last line of code in the measure Sell through:
IF( and( NOT( ISBLANK(SellThroughH)),CumPurchasesQ <> 0),SellThroughH)

It was before IF( NOT( ISBLANK(SellThroughH)),SellThroughH)

1 Like