Your first on-time delivery dashboard in Power BI

On-time delivery is the number every logistics manager asks about. Here is how to build it in Power BI Desktop, which is free to download from Microsoft.

What you need

A table of shipments with at least these columns: shipment number, promised date, delivered date. Save it as an Excel file or a CSV file. We will call the table Shipments.

Load the data

1. Open Power BI Desktop and choose Get data, then Excel workbook (or Text/CSV).
2. Select your table and click Transform Data.
3. Check that the date columns have the Date data type, then click Close & Apply.

Create a calendar table

A calendar table lets you group results by month. In the Modeling tab choose New table and enter:

Calendar = CALENDARAUTO()

Then create a relationship by dragging Calendar[Date] to Shipments[Delivered Date] in the Model view. Add month and year columns to the calendar if you want (for example Month = FORMAT(Calendar[Date], "MMM yyyy") and a sort column).

Write the measures

A measure is a calculation that Power BI works out for whatever you are looking at, such as one month or one customer. Create three, using New measure:

Delivered Shipments =
CALCULATE(
    COUNTROWS(Shipments),
    Shipments[Delivered Date] <> BLANK()
)

On-Time Shipments =
COUNTROWS(
    FILTER(
        Shipments,
        Shipments[Delivered Date] <> BLANK()
            && Shipments[Delivered Date] <= Shipments[Promised Date]
    )
)

On-Time % =
DIVIDE([On-Time Shipments], [Delivered Shipments])

DIVIDE returns blank instead of an error if there are no deliveries, which is safer than using the / symbol.

The mistake that inflates your numbers

If a shipment has not been delivered yet, its delivered date is blank. In DAX, a blank date can behave like zero, which counts as "before the promised date". Your undelivered shipments then look on time. That is why both measures above exclude blank delivered dates. Skipping this check makes your on-time percentage look better than it really is.

Build the visuals

1. Add a Card visual and drop On-Time % into it. Format it as a percentage.
2. Add a Line chart. Put the month on the X axis and On-Time % on the Y axis.
3. Add a Bar chart with Customer or Lane on the axis and On-Time % as the value. This shows where the problems are.
4. Add a Slicer for year or customer so people can filter.

Share it

Sharing reports online needs a Power BI account with the right licence, which depends on your company. For many people, the simplest start is to export the report to PDF from the File menu and email that.

Clean data makes this much easier. See Power Query for monthly files to prepare your data first.