Forum Discussion
Helper IICreating an enddate based on total months active
Hi I am trying to find a way to make an end date based on a value I have in another table, I cant get it to reference it in the DAX query tho and since I havent really seen anyone else do this I came here asking for help. Below you can see my data model:
In the "Orders" table there is a field named "Orderdatum" this is the start date, in the table "Orderinformatie" there is a field named "Hoeveelheid" which contains one of the following values: 1,3,6,9,12. These are the amount of months that the contract is active. I want to be able to create an end date so I can use other formulas to get the revenue spread out over the period of the contract instead of just the start date.
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.
13 Replies
- timalbers
Skilled Sharer
Hi Rbakker888
you may want to create a new calculated column in the Orders table.
If the tables are properly related in the model, you could use this:EndDate = DATEADD( Orders[Orderdatum], RELATED( Orderinformatie[Hoeveelheid] ), MONTH )
Or as an alternative if there is no suiting relationship:EndDate = VAR MonthsToAdd = LOOKUPVALUE( Orderinformatie[Hoeveelheid], Orderinformatie[OrderID], Orders[OrderID] ) RETURN DATEADD( Orders[Orderdatum], MonthsToAdd, MONTH )
Rbakker888Hi timalbers
Seems like this has turned into a much more complicated matter probably, I did not make our database but I just discovered that the OrderID in the orderinformatie table is not unique and is more a expansion of the order as multiple products are inside the order. There is another column named Sorteernummer which combined with the order-Id will give me a unique value but I want to see all the products in the order in 1 row.
All the products in the table have the same data so that can just be merged except for the sell price which would need to be put together. Would you be able to help me with this or should I make a seperate post as I honestly dont know how to begin here.- timalbers
Ok, in this case the new calculated column in your Orders table will look something like this:
EndDate = VAR MonthsToAdd = CALCULATE( MAX( Orderinformatie[Hoeveelheid] ), FILTER( Orderinformatie, Orderinformatie[OrderID] = Orders[OrderID] ) ) VAR result = DATE( YEAR( Orders[Orderdatum] ), MONTH( Orders[Orderdatum] ) + MonthsToAdd, DAY( Orders[Orderdatum] ) ) RETURN resultThis will return the end date.
After that you may want to create a measure for the calculation of the total sell price:
TotalSellPrice = CALCULATE( SUM( Orderinformatie[SellPrice] ), FILTER( Orderinformatie, Orderinformatie[OrderID] = MAX( Orders[OrderID] ) ) )
Finally for having the products all in the same row, you can concatenate them in another measure like so:ProductsList = CONCATENATEX( FILTER( Orderinformatie, Orderinformatie[OrderID] = MAX( Orders[OrderID] ) ), Orderinformatie[ProductName], ", " )This returns a string of all products seperated with a comma.
Maybe you'll need to replace some column names with the correct ones of your model.