Forum Discussion
datediff between first and lastdate
Hi,
I need a measure to calculate the time between the first and last delivery date per Purchase order
The table is such as:
| Receivedorderline | Purchase order | Receivedate |
| 1 | 1 | 1-1-2017 |
| 2 | 1 | 15-1-2017 |
| 3 | 1 | 17-1-2017 |
| 4 | 2 | 2-2-2017 |
| 5 | 2 | 3-2-2017 |
| 6 | 2 | 3-2-2017 |
| 7 | 3 | 3-3-2017 |
| 8 | 3 | 3-3-2017 |
Now i want the time between the first and last receivedate per purchase order
(example purchase order 1. first date = 01-01-2017 and lastdate = 17-1-2017. Measure datediff = 16 days
greetings, Norbertus
Anonymous
hi, Use a measure
DaysDiff = DATEDIFF(Min(Table1[Receivedate]),MAX(Table1[Receivedate]),DAY)
Now Put in a visual the PO and this Measure.
3 Replies
Anonymous
hi, Use a measure
DaysDiff = DATEDIFF(Min(Table1[Receivedate]),MAX(Table1[Receivedate]),DAY)
Now Put in a visual the PO and this Measure.
Hi Anonymous,
An alternative is to create a new table using dax Summarize: If you do not know: To create a new table go to the tab: modeling>
New TableDax to create the table:
Table2 = SUMMARIZE(Table1; Table1[Purchase order]; "MinDate"; MIN(Table1[Receivedate]); "MaxDate";MAX(Table1[Receivedate]); "Total"; MAX(Table1[Receivedate]) - MIN(Table1[Receivedate]) )
Hi,
Is it really necessary to create a new table? After this i think my Datamodel isn't 'nice' anymore
Because i merged before receivedorders and receivedorderlines to one new table (as a fact table)
When i make 2 seperate tables i can also make a calculated column in receivedorders like:
Column = CALCULATE((MAX(receivedorderlines[Receivedate])-MIN(receivedorderlines[Receivedate])*1);
FILTER(receivedorderlines;receivedorderlines[order]=receivedorder[order]))
