datediff between first and lastdate

[Anonymous]

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

[Vvelarde · Accepted solution]

@Anonymous

hi, Use a measure 

DaysDiff = DATEDIFF(Min(Table1[Receivedate]),MAX(Table1[Receivedate]),DAY)

Now Put in a visual the PO and this Measure.

[EvertonPessoa]

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 Table

Dax 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])
		   )

[Anonymous]

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]))