On-time Deliveries Analysis

DAX DAX Calculations
1

Hello,

I’m trying to put together a report on on-time delivery.

I have some order numbers, products, quantities, and sales.
I then have a document date, a requested delivery date and a actual delivery date.

I’m trying to run a report to see.
///number of days +/- per document number
Processing Time = DATEDIFF(SELECTEDVALUE(‘Transaction Data’[Requested Date]),SELECTEDVALUE(‘Transaction Data’[Ship Date]),DAY)

///field to show early, on-time or late
Status = SWITCH(TRUE(),[Processing Time]<0,“Early”,[Processing Time]=0,“On-Time”,[Processing Time]>0,“Late”)

///a calculation to count number of days early per day
/// a calculation to count number of days on-time per day
/// a calculation to count number of days late per day
/// a calculation to count the number of documents per day

Failed on these…
Here’s one example
Number of Early = COUNTX(‘Key Measures’,[Status]=“Early”)
I assume they would all be the same mostly

Then I’m running into difficulty because I want to be able to show a line chart

axis: date and number of days
Values: (1) number of days early, (2) number of days on-time, (3) number of days late

The other area I am running into is calculating and average number of days per given period

and most importantly, I am trying to calculate the percentage of Deliveries that are Early or On-time, compared to all orders.

Not sure how to calculate these few things. I’ve been researching all day, but can’t seem to find answers to these questions.

I’ve attached my query if anyone has suggestions or links to articles, videos, showcases, etc that cover this topic, that would be awesome.

On-time Deliveries.pbix (149.0 KB)

2

@ibesmond
I started off by converting the Dates in Transactions Table to Date Format as previously they were in Text format. Then marked the Dates as Date Table.
Instead of creating measures for processing time and status I created the calculated columns (it can also be done via power query). We need the calculated columns so we can count the status like on-time, early and late. The end result looks like this:

1
11307×763 66.1 KB

Attaching the source and pbix file for your reference.

Transaction Data.csv (76.7 KB)
On-time Deliveries.pbix (142.6 KB)

Let me know if this works.

Thanks.

4 Likes
3

Hi @ibesmond, we’ve noticed that no response has been received from you since the 8th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

Hi @ibesmond, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

5

@MudassirAli,

Thank you so much for the solution. I am still running into one issue which I can’t seem to figure out.

In my example data I didn’t include Actual Ship Dates that haven’t occurred yet.

For example, a Requested Ship Date of 12/30/2020, but it has not shipped yet, since it should go out on 12/30/2020 a future date.

I tried to write a expression using if or switch and it seems to be working, however I don’t know how to exclude weekends from the count. In other words to not count the days on the weekend. So if I Requested Ship Date was on a Friday, and the shipment didn’t go out until Monday, it would show as only being one day late, not three days late.

Processing Days =
SWITCH( TRUE(),
‘Sales Transactions’[Actual Ship Date] = DATE(1900, 1, 1) && ‘Sales Transactions’[Requested Ship Date] > TODAY(),
BLANK(),
‘Sales Transactions’[Actual Ship Date] <= DATE(2020, 1, 1) && ‘Sales Transactions’[Requested Ship Date] <= TODAY(),
DATEDIFF(
‘Sales Transactions’[Requested Ship Date],
TODAY(), DAY ),
‘Sales Transactions’[Actual Ship Date] <> DATE(2020, 1, 1),
DATEDIFF(
‘Sales Transactions’[Requested Ship Date],
‘Sales Transactions’[Actual Ship Date], DAY ) )

Appreciate the help.

6

@ibesmond Can you share your latest PBIX file so I can work on it?
Moreover, it’s advisable to create another post as it has been already marked as solution. It will be easier in future for members to see the solutions on relevant posts.

Thanks.