return number based on 3 date entries - but one can have empty values

[Cool_McCool]

Hi everybody,

I am using power pivot 2016 on windows 10 enterprise.

I have a data model an one of the sheets is a back order list. In this sheet I have the order creation date, the customer delivery wish date and the supplier (to us) confirmed del. date. If the supplier has not confirmed this can be blank.


I have tried logical values and datediff. I do not know what else to try. I would like to set priorities (for a slicer in the dashboard) based as follows.

Thanks for any tips! I am going crazy.

Prio	Created on	Cust. Wish Date	Supplier Confirmed Date
1	any	overdue (date is earlier than today but does not equal creation date)	overdue earlier than today
2	older 10 days	any	empty
3	any	any	overdue earlier than today
4	any	earlier than supp confirmed	later than customer wish (if wish does not equal created on date)
5	anything else	anything else	not overdue

<tbody>
</tbody>