Difference between two dates

[Rasmusrock]

Hi,

I am looking to make a table that shows me when an order is: on time, arrives before time or is late, and how late or early it is.

This is given by two dates: 

'Shipment date' minus 'Confirmed date'  -----> ('Afskibning'  'Bekrxftet' in Danish)

Right now, i have combined the two calculated columns, and the calculated measure below:

Columns:
=[Afskibning]-[BEKRXFTET]

=IF([Forskel_afskibning]>1000;BLANK();IF([Forskel_afskibning]<-1000;BLANK();[Forskel_afskibning])) --> To sort out any rows where the date is blank (01-01-1900)

Measure:

Positiv leveringsevne=CALCULATE(SUM(Indlinjer[Leveringsevne_afskibning]);Indlinjer[Leveringsevne_afskibning]>0) --> To only give me the order that arrived ahead of the scheduled time

Which gives the me below result, where '13296' is the order number, and the below figures are the items in the order.

13296	1	196
Cb0850	1	49
Cb0851	1	49
Cb0852	1	49
Cb0853	1	49

However, using my calculation it sums up how early each item in the order is (49 in this example) which adds up to 196 for the four items. Therefore, i am looking for a way for the formula to not show how early each item is, but only how early (or late) an order is, while still being able to show the items in the order. 

Hope someone can help!

/Rasmus

[DataChant]

Hi Rasmus,

Could you provide more information about your table schemas?

Do you actually have a table with both orders and items on the same column? If so, how do identify that a row is an order and not an item?

If the table below is a visual with orders and nested items, you can use HASONEVALUE on your items column (Replace the commas with semicolons for your enviornment):

New Measure for Orders Only = IF ( HASONEVALUE ( <Items Table and Column Name> ) , BLANK() ,  [Positiv leveringsevne] ) 

Hope it helps,

Gil

[Rasmusrock]

Thanks @DataChant, it partly solves my problem, however not the most essential part of the problem.. Ill try to explain in detail:

As you see in the below picture, the left pivot is my current result, and the right result is when i apply your formula. The numbers are the same, however, i am not able to show the items in the orders.

 

What i want is presented in the next picture, where i tried to explain it a bit more detailed:

The left table shows the denotation of the table, with three layers: Creditor, Order and item, and a given order can only be positive or negative (Positive = Early arrival, Negative = Late arrival).

The middle table shows my current status, where the "earliness" of the order is given by summing the "earliness" of each item in the order. However, i want it to only sum the amount of time a specific order is early (as shown in table 3), and not how early each item in the order is.

Does it make sense?

/Rasmus

[v-ljerr-msft]

Hi @Rasmusrock,

Could you post your table structures with some sample data, and the measures you are using in this case? It's better to share a sample pbix file.

Regards