Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating new table with min values from another table

Dear everyone,

 

I have problems filtering the values for my new table.

I have a table with a lot of forecasts per customer; a date when the forecast was made, a date when forecast is valid, value of the forecast and customer id. Well what i want to get in a new table is:

Minimum difference between when the forecast was made and was valid on, value of the forecast and customer id. 

Here is my first table:

 The output i want is:

 

So combination of cust_id and valid can only appear once, and is determined by the "closest" forecast, it's date is given by issued.

 

Hopefully this is not too complicated, but i am really lost here.

 

P.S.

Unfortunately i can't just set the difference to 0 or 1 because some days there are no forecast and the min(difference) could be 2 or 3.

Need Help
Show and Tell
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    You can new a calculated table like this:

    New Table =
SUMMARIZE (
    table,
    table[custid],
    table[issued(LT)],
    table[valid(LT)],
    "Min diff", MIN ( table[diff issued/valid] )
)


    Best regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    You can new a calculated table like this:

    New Table =
SUMMARIZE (
    table,
    table[custid],
    table[issued(LT)],
    table[valid(LT)],
    "Min diff", MIN ( table[diff issued/valid] )
)


    Best regards,

    Yuliana Gu