cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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:Knipsel.PNG

 The output i want is:

 

Knipsel.PNG

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Creating new table with min values from another table

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Creating new table with min values from another table

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.