Showing True if Date is is on or before date B

[Nikon1177]

Hi all, 

New to PowerBI but have a background in SQL. 

I'm trying to show On Time Delivery %, in order to calculate that I need to know if a delivery was late or not.

What i need is a way to show if (Table1)[Delivered Date] is on or before (Table2)[Due Date].

What's the best way to go about this?

[v-qiuyu-msft · Accepted solution]

Hi @Nikon1177,

In your scenario, you need to create a calculated column to return [Due Date] from Table2. Then create another column to decide whether the delivery is late or not. See:

Due Date from Table2 = RELATED(Table2[Due Date])

Column = IF(Table1[Delivered Date]<=Table1[Due Date from Table2],"On-Time","Late")

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Nhallquist]

The way that I have done this in the past is quite simple.  

Late Order Indicator = IF(Delivered Date>Due Date, "Late", "On-Time")

YOu may need to do some formatting of your dates, so you just have the date, and not the entire Timestamp.  If you want to get a feel for how late something is, simply subtract the dates, to get the interval.  

Once you get your indicator set, you can simply count the "On-Time"Orders, and divide by the total count of orders.  

Another way to do this would be to do this:

Late Order Indicator = IF(Delivered Date>Due Date, 1,0)    //1=Late    0=On-Time

To calculate the on-time %, you can sum the Late Order Indicator, assuming you have only 1 order record, per order, and then count the total number of orders (or Distinct Count).

Good luck...

Nate

[Nikon1177]

The issue that I'm running into with this is that the two dates are on two differnt tables, the tables have an indriect relationship.

What I get when trying your suggested formula is "A single value for column 'delivered date' in table 'invoice_header' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I thought that the indirect relationship that allowed them to be displayed on the table together would be enough, apparently it's not?

[Sean]

@Nikon1177 Are you creating a COLUMN?

I just quikly scanned this post but from what you are describing looks like you need a COLUMN and you are creating a MEASURE!

[Anonymous]

You are almost *certainly* going to want to add another table that is your "calendar table", and use that to tie your fact tables together.

[Nhallquist]

So you have an invoice header.  What is the other table?  can you also post a pic of the data model?