Find minimum of a measure that refers to multiple columns in different tables

[Anonymous]

Hi,

I have two tables as the below.

I want to select a date in the future and see if we can deliver it from stock. As for instance:

Today is the 25-03-2020 and I want to deliver a plant containing ITEM A and ITEM B the 27-03-2020.

It means that I have 2 days to deliver this. Lets say that the leadtime for getting these home is 20 days. It means that I will have to have theses on stock or else I can't deliver the plant.

So what I do is to find the minimum accumulated stock in the period from today looking 20 days forward which is my first measure and is taken from TABLE A. This I divide with my second measure which is the quantity per item that I need for the plant which is taken from TABLE B.

If the minimum stock in this period is lower than the quantity I need to create the plant then I can't deliver from stock because it is already taken from another order. If it is higher then I can deliver.

So:

Measure 1 = minimum stock in the period from today and 20 days forward

Measure 2 = quantity needed for the plant

This I put in to a third measure:

Measure 3 = Measure 1 / Measure 2

If this measure is below 1 then it is not possible to deliver from stock or else I can deliver.

The measure 3 is done for each item which means both ITEM A and ITEM B.

In PowerBI I only want to show the minimum value of measure 3 - AND NOW TO MY QUESTION!

How do I find the minimum value of a measure that is calculated based on two different tables?

----------------------------------------------

Table A - Accumulated stock level:

    Date         ITEM        QTY     ACC QTY

1-1-2020     ITEM A        1             1

2-1-2020     ITEM A        1             2

1-1-2020     ITEM B        4             4

2-1-2020     ITEM B       -3            1

----------------------------------------------

TABLE B - Plant overview: 

  ITEM        QTY per Plant

ITEM A           2

ITEM B           3

[v-juanli-msft]

Hi @Anonymous 

Check if Greg_Deckler's answer help you.

If not, please show your expected result based on your example.

Best Regards

Maggie

[Greg_Deckler · Accepted solution]

Perhaps:

Measure 4 =

  VAR __Table =

    ADDCOLUMNS(

      'Table A',

      "Measure 3",[Measure 3]

    )

RETURN

  MINX(__Table,[Measure 3])

[Anonymous]

Thanks for the solution - It worked 👍

[Greg_Deckler]

Great!