Forum Discussion
Helper IICreating an enddate based on total months active
Well, actually the MAX should not be crucial, as you said that for every order all the products in the table have the same data except for the sell price. So basically you could also take MIN() instead of MAX(), it should not matter at all.
CALCULATE( MAX( Orderinformatie[Hoeveelheid] ), FILTER( Orderinformatie, Orderinformatie[OrderID] = Orders[OrderID] ) )This statement filters the Orderinformatie table down to the exact orderID for every row in the order table. Out of this subset of products from a unique orderID it takes the MAX value of Hoeveelheid. It could as well take the MIN value - should not matter as a single order should havethe same value for each product.
Helper IISo maybe you understood me wrong but I want the end date per distinct order-ID, this formula does work so thats good only now its only skipping a year every time due to the value it returns being 12. Cause of the max I assume
Skilled SharerWell, actually the MAX should not be crucial, as you said that for every order all the products in the table have the same data except for the sell price. So basically you could also take MIN() instead of MAX(), it should not matter at all.
CALCULATE(
MAX( Orderinformatie[Hoeveelheid] ),
FILTER( Orderinformatie, Orderinformatie[OrderID] = Orders[OrderID] )
)This statement filters the Orderinformatie table down to the exact orderID for every row in the order table. Out of this subset of products from a unique orderID it takes the MAX value of Hoeveelheid. It could as well take the MIN value - should not matter as a single order should havethe same value for each product.
- Rbakker888
Thank you, it seems it was the related function I used before that wasnt correctly doing the relation between the 2 tables, when removed it now works.
Thank you for your help!
My final formula for anyone reading this later:EindDatumContract =VAR MonthsToAdd =COALESCE(CALCULATE(MAX( Orderinformatie[Hoeveelheid] ),FILTER( Orderinformatie, Orderinformatie[Order-id] = (Orders[Order-id]))),0)VAR result = EDATE( Orders[Orderdatum], MonthsToAdd)RETURN result - timalbers
Glad to help, have a great day!