Forum Discussion

floppy_fish's avatar
floppy_fish
Regular Visitor
5 years ago
Solved

Calculating Sell-In since a specific launch date - NEED PRECIOUS HELP

Hi the community ! 

 

I need your help to solve this problem where I'm stuck in my company : 

 

- I tried to calculate how many units (sell-in) has been sold by UPC since their launch date for the first 12 months 

- Each UPC may been launch in a different month (Jan, Feb..) et a different year (2020, 2019, 2018...) 

 

So far I came with this formula but it still doesn't work : 

 

SI by LT =

CALCULATE(

SUMX(BOOST_Sell_In,BOOST_Sell_In[Sell In (UN)]),

DATESINPERIOD(Dates[Date],

MIN('Launch Timing'[Date of Launch]),

12,MONTH))

 

For your info : 

 

Tab boost_Sell_in = Sell In 

Tab Launch Timing = It's the tab with the launch date by UPC. 

 

Your help will be really appreciate on this ! 

 

Many thannnks !

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    floppy_fish , this seems correct. Hope your displaying data by UPC and dates has been marked as date tbale

     

    CALCULATE(
    SUM(BOOST_Sell_In[Sell In (UN)]),
    DATESINPERIOD(Dates[Date],MIN('Launch Timing'[Date of Launch]),12,MONTH))

     

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, floppy_fish 

    Please check the below picture and the sample pbix file's link down below.

    My sample is to find the first 120 days sales qty after the first launching date.

    I hope you can apply a similar logic to your case.

    If you can share your sample pbix file's link, I can try to have a look and come up with more relevant measures.

     

    In my fact table, you can see that I created a new column, which is the most important step in this case. Once this new column is created, then the next steps will become quite easy to move forward.

     

     

    https://www.dropbox.com/s/arlbl9ymvs3qr5r/floppy.pbix?dl=0 

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • floppy_fish's avatar
      floppy_fish
      Regular Visitor
      5 years ago

      Hi Jihwan_Kim 

       

      First of all, thank you so much for your help and the pbi exemple provide ! All my team are happy to feel support by you ! It's really appreciated.. 

       

      Unfortunaly it doesn't work with our dataset... To avoid sharing sensible data, i provide you via this link below an exemple of the two main table where I'm working with (we have also a calendar table). 

       

      https://www.dropbox.com/scl/fi/yhyqe4phcyrsdwakte6k6/Exemple_Sell-In-Since-launch_data.xlsx?dl=0&rlkey=zsb9rq1dl02hpof47bsv4ufzm

       

      If the link doesnt work let me know if i can send you info by email ? 

       

      Tge goal is to have by month how much Sell-In we cumulated since the first date of launch... 

       

      The date of launch is on a other table... 

       

      But maybe if it takes into account the first date of sell-in in the boost tab it could works ? 

       

      Thanks again for your help ! 

       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        Hi, floppy_fish 

        Thank you for sharing the data.

        I am not sure if I understood your question correctly. The first question was comparing the first 12 months' cumulative values of each item that has a different starting date.

        I think your sample has the same starting date, and I am not sure if I needed to write measures to consider the different starting dates.

        I just created a cumulate sum line chart, and please have a look whether it is what you are looking for.

         

         

         

        https://www.dropbox.com/s/brwqn5m9mkjdv8m/floppyv2.pbix?dl=0 

         

         

        Hi, My name is Jihwan Kim.

         

        If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

         

        Linkedin: linkedin.com/in/jihwankim1975/

        Twitter: twitter.com/Jihwan_JHKIM