cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reuben Helper II
Helper II

Relate production date with nominal speed

Hi all,

 

I came up with the following problem:

I have two tables:

- The data table shows daily production by production line.

- The lookup table contains the nominal speed of each production line. It is a fixed value that only changes when the production line is changed or an improvement is  made.

In my example production line1 was working at 5.000 unit/hour from 01/07/2014 until 09/04/2017, at 5.500 from 10/04/2017 to 17/02/2019, and at 6000 from 18/02/2019 until now.

What I want is a DAX measure, that relates for each produccion date, the corresponding nominal speed.

 

Thank you very much for yor help.

Reubben

 

 

Lookup Table = Nominal Speed   Data Table = Production  
          
Production LineStarting dateSpeed unit/hour   Production DateLineUnitsSpeed/hour
Line101/07/20045.000   01/07/2015Line120.0005.000
Line110/04/20175.500   30/01/2018Line125.0005.500
Line118/02/20196.000   01/01/2019Line135.0006.000
Line201/07/200410.000       
Line301/07/20049.000       
Line310/08/201610.000       
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support
Community Support

Re: Relate production date with nominal speed

 Hi @Reuben 

 

Firstly, create column Speed/hour to meet your demand.

 

Speed/hour = maxx(TOPN(1,FILTER('Nominal Speed','Nominal Speed'[Production Line]=Production[Line]&&'Nominal Speed'[Starting date]<=Production[Production Date]),'Nominal Speed'[Starting date],DESC),'Nominal Speed'[Speed unit/hour])

 

Then, choose table visual to display the result.

 

6.png

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfB-bp4PE1xEjXtUso...

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Reuben Helper II
Helper II

Re: Relate production date with nominal speed

Thank you very much @v-xicai  for your help. and for attaching the test file. much appreciated!!

View solution in original post

2 REPLIES 2
Community Support
Community Support

Re: Relate production date with nominal speed

 Hi @Reuben 

 

Firstly, create column Speed/hour to meet your demand.

 

Speed/hour = maxx(TOPN(1,FILTER('Nominal Speed','Nominal Speed'[Production Line]=Production[Line]&&'Nominal Speed'[Starting date]<=Production[Production Date]),'Nominal Speed'[Starting date],DESC),'Nominal Speed'[Speed unit/hour])

 

Then, choose table visual to display the result.

 

6.png

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfB-bp4PE1xEjXtUso...

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Reuben Helper II
Helper II

Re: Relate production date with nominal speed

Thank you very much @v-xicai  for your help. and for attaching the test file. much appreciated!!

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors