Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
floppy_fish
Regular Visitor

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 !

2 ACCEPTED SOLUTIONS

Hi @amitchandak  ! 

 

Thanks for your help ! 

 

You could find below a sample data of what we use with no sensitive data... 

 

Thank you so much for providing your help we have lot of stuggle in this task... 

 

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

 

View solution in original post

Hi, @floppy_fish 

Please check the below picture and the link down below.

One additional calculated column has been created in BoostSellIn table.

I think this column is the critical column in this case, because this column is used as X-Axis in the line chart.

 

Picture4.png

 

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

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


Go to My LinkedIn Page


View solution in original post

8 REPLIES 8
Jihwan_Kim
Super User
Super User

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.

 

Picture5.png

 

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

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


Go to My LinkedIn Page


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&rlk...

 

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 ! 

 

 

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.

 

Picture2.png 

 

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

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


Go to My LinkedIn Page


Hey @Jihwan_Kim 

 

Thanks for replying so fast ! 

 

Yes it's my mistake... Please find below an update of the file with correcting value : 

 

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

 

What I need is : 

 

- Look All Sell-In by UPC since their date of launch for the next 12 Months. 

- All UPC have a different launch date. 

- I need to take the launch date in an other tab to do it ! 

 

Sorry if it was not clear... 

 

I guess I need a formula with a MIN or EARLIER (but this one I don't know how to use it correctly)... 

 

Thanks again for your help !!! 

 

Hi, @floppy_fish 

Please check the below picture and the link down below.

One additional calculated column has been created in BoostSellIn table.

I think this column is the critical column in this case, because this column is used as X-Axis in the line chart.

 

Picture4.png

 

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

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


Go to My LinkedIn Page


Hi @Jihwan_Kim !

 

I don't know how to thank you, but it's finaly working !! 

 

You can't imagine how many happiest person you made with your precious help ! 

 

In the same time you learn me a lot... I never use "EARLIER" and "DATEDIFF", and try to understand their specific use, compare to MIN by exemple... 

 

Again, thank you so much for offering your help ! 

 

I wish you all the best 🙂

 

Rafael

amitchandak
Super User
Super User

@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.

Hi @amitchandak  ! 

 

Thanks for your help ! 

 

You could find below a sample data of what we use with no sensitive data... 

 

Thank you so much for providing your help we have lot of stuggle in this task... 

 

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors