cancel
Showing results for
Did you mean:
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 Line Starting date Speed unit/hour Production Date Line Units Speed/hour Line1 01/07/2004 5.000 01/07/2015 Line1 20.000 5.000 Line1 10/04/2017 5.500 30/01/2018 Line1 25.000 5.500 Line1 18/02/2019 6.000 01/01/2019 Line1 35.000 6.000 Line2 01/07/2004 10.000 Line3 01/07/2004 9.000 Line3 10/08/2016 10.000
2 ACCEPTED SOLUTIONS

Accepted Solutions
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.

Best Regards,

Amy

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

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!!

2 REPLIES 2
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.

Best Regards,

Amy

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

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!!

Announcements

#### 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!

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

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

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

Top Solution Authors
Top Kudoed Authors