Relate production date with nominal speed

[Reuben]

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

[v-xicai · Accepted solution]

 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.

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.

[v-xicai · Accepted solution]

 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.

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.

[Reuben · Accepted solution]

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