Creating new table with min values from another table

[Anonymous]

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.

[v-yulgu-msft · Accepted solution]

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