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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Prio

[/TD]
[TD]Created on

[/TD]
[TD]Cust. Wish Date

[/TD]
[TD]Supplier Confirmed Date

[/TD]
[/TR]
[TR]
[TD]1

[/TD]
[TD]any
[/TD]
[TD]overdue (date is earlier than today but does not equal creation date)

[/TD]
[TD]overdue earlier than today
[/TD]
[/TR]
[TR]
[TD]2

[/TD]
[TD]older 10 days
[/TD]
[TD] any

[/TD]
[TD]empty
[/TD]
[/TR]
[TR]
[TD]3

[/TD]
[TD]any
[/TD]
[TD] any
[/TD]
[TD]overdue earlier than today

[/TD]
[/TR]
[TR]
[TD]4

[/TD]
[TD]any
[/TD]
[TD]earlier than supp confirmed
[/TD]
[TD]later than customer wish (if wish does not equal created on date)
[/TD]
[/TR]
[TR]
[TD]5

[/TD]
[TD]anything else

[/TD]
[TD]anything else

[/TD]
[TD]not overdue

[/TD]
[/TR]
</tbody>[/TABLE]