Same Day Delivery Status

[mahra-in]

Hi

Below is the sample of table I have

Order	Line	Promised Date	Actual Date	Diff Days	Status
1234	1	12-Feb-21	08-Feb-21	-4	Ontime
1234	2	12-Feb-21	15-Feb-21	3	Delay
1234	3	15-Feb-21	11-Feb-21	-4	Ontime
1234	4	12-Feb-21	13-Feb-21	1	Delay
3322	1	01-Feb-21	02-Jan-21	-30	Ontime

In the above table, there are 2 Unique Orders.

If we consider Order 1234, Line 1  & 2 has same Promised Date which will be treated as single delivery and if one of the Line in a particular order with same Promised date has status as "Delay" then that single delivery (both Line 1 & 2) will be treated as 1 Delay.

And Line 3 in order 1234 is another delivery which has Ontime and Line 4 is another delivery which has Delay.

Line 1 & 2 as 1 Delivery, Line 3 as 1 Delivery & Line 4 as 1 Delivery - Total 3 Deliveries. 

Out of the Total 3 Deliveries,

Line 1 & 2 together treated as Delay since if atleast 1 or all has Delay status

Line 3 is Ontime

Line 4 is Delay

So only 1 Ontime

End Result for Order 1234 is 1/3 = 33% is the expected result. This is only for Order 1234

If I consider all the lines in table, there are 4 deliveries, out of which 2 is Ontime. 

So the End Result I want is 2/4 = 50%.

Can you please help to calculate this in Power BI

[mahra-in]

Yes this is only part of my data. If other rows have same delievry date & same order number then it will be considered as one delivery

[v-kelly-msft · Accepted solution]

Hi  @mahra-in ,

First create an index column;

Then create 3 calculated columns as below:

_is ontime = 
var _previous =CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[Order]=EARLIER('Table'[Order])&&'Table'[Promised Date]=EARLIER('Table'[Promised Date])&&'Table'[Index]<EARLIER('Table'[Index])))
Return
IF(_previous<>BLANK(),BLANK(), CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Order]=EARLIER('Table'[Order])&&'Table'[Promised Date]=EARLIER('Table'[Promised Date])&&'Table'[Status]="Ontime")))

_is delay = 
var _previous =CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[Order]=EARLIER('Table'[Order])&&'Table'[Promised Date]=EARLIER('Table'[Promised Date])&&'Table'[Index]<EARLIER('Table'[Index])))
Return
IF(_previous<>BLANK(),BLANK(), CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Order]=EARLIER('Table'[Order])&&'Table'[Promised Date]=EARLIER('Table'[Promised Date])&&'Table'[Status]="Delay")))

exclusion = IF('Table'[_is delay]=BLANK(),0,ABS('Table'[_is delay]-'Table'[_is ontime]))

 Then create 2 measures as below:

Measure for order = 
var _numerator= CALCULATE(SUM('Table'[_is ontime])-SUM('Table'[exclusion]),FILTER(ALL('Table'),'Table'[Order]=MAX('Table'[Order])))
var _denominator=CALCULATE(SUM('Table'[_is ontime])+SUM('Table'[_is delay])-SUM('Table'[exclusion]),FILTER(ALL('Table'),'Table'[Order]=MAX('Table'[Order])))
Return
DIVIDE(_numerator,_denominator)

Measure for total = 
var _numerator= CALCULATE(SUM('Table'[_is ontime])-SUM('Table'[exclusion]),ALL('Table'))
var _denominator=CALCULATE(SUM('Table'[_is ontime])+SUM('Table'[_is delay])-SUM('Table'[exclusion]),ALL('Table'))
Return
DIVIDE(_numerator,_denominator)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi @mahra-in ，

Is it part of your data or all of data?If other rows have the same delivery date and same order number,will them be considered as one delivery either?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!