Date diff based on first date and last date

[Boycie92]

Hi

I’m wondering if someone can help.

I have a model set up using direct query and I have the following formula

DateDiff =

IF ( MAX(Table[OrderComplete]) = DATE ( 9999, 12, 31 ) && MAX(Table[OrderShipped]) = DATE ( 9999, 12, 31 ), BLANK(),

IF( MAX(Table[OrderComplete]) <> DATE ( 9999, 12, 31 ) && MAX(Table[OrderComplete]) = DATE ( 9999, 12, 31 ),

        DATEDIFF ( MIN(Table[OrderComplete]), TODAY (), DAY ),

        DATEDIFF ( MIN(Table[OrderComplete]), MAX(Table[OrderShipped]), DAY )

    ))

 I want the formula to work in the following ways:

• Calculate the date difference between the earliest OrderComplete date and the Last Ordershipped date
• If any dates in the OrderCompleted and OrderShipped date columns are 31/12/9999 then don’t calculate the date difference show me a blank
• If the OrderCompleted Date is NOT 31/12/9999 and the OrderShipped IS 31/12/9999 then calculate the date difference from the OdrerCompleted date to TODAY

My data contains histories (if a records change a new row appears)

I also have multiple orders (which has its own InstanceNumber) per customer (that have a UniqueID)

I have a problem with customers that have multiple records

if they only have the ONE order and the OrderCompleted and OrderShipped date columns are 31/12/9999 then show me a blank

If there are multiple orders show me the date difference between the first OrderCompleted date and the last OrderShipped date that is NOT 31/12/9999

Is there any way to change my formula to do this or should I just replace all 31/12/9999 with nulls?

Thanks in advance

Boycie92

[v-yulgu-msft · Accepted solution]

Hi @Boycie92,

Please try this:

DateDiff =
VAR FirstOrderComplete =
    CALCULATE (
        MIN ( Table[OrderComplete] ),
        ALLEXCEPT ( table, table[CustomerID] )
    )
VAR LastOrderShipped =
    CALCULATE (
        MAX ( Table[OrderShipped] ),
        ALLEXCEPT ( table, table[CustomerID] )
    )
RETURN
    IF (
        FirstOrderComplete = DATE ( 9999, 12, 31 )
            && LastOrderShipped = DATE ( 9999, 12, 31 ),
        BLANK (),
        IF (
            FirstOrderComplete <> DATE ( 9999, 12, 31 )
                && LastOrderShipped = DATE ( 9999, 12, 31 ),
            DATEDIFF ( FirstOrderComplete, TODAY (), DAY ),
            DATEDIFF ( FirstOrderComplete, LastOrderShipped, DAY )
        )
    )

Regards,

Yuliana Gu

[Boycie92]

Hi @v-yulgu-msft

Thanks for this.

I have used it within the table visual and the calculation seems to ignore a visual level filter I have on the chart to only show items that have not been shipped (based on the 12/31/9999) date. Is there any reason for this?

Also, I have an additional issue that I have noticed, I know you have accounted for this in your formula but I am having records that have 12/31/9999 as their order date and the shipped date.

For example

OrderDate                           ShippedDate                     DateDiff

31/01/2013                         21/3/2013                            151

31/12/9999                         31/12/9999

31/12/9999                         31/12/9999

08/11/2013                         08/04/2014                         49          

Total                                                                                1838

It seems to be calculating it from the 31/1/2013 to the 31/12/9999. Per record the calculation is correct. However, the final total is not. Is there any way to fix this?

Thanks,

Boycie92

[Boycie92]

Hi,

Does anyone else know how to fix this?

Thanks,

Boycie92

[cthurston]

First to solve the multiple records setup a Matrix with Customer and Instance below it in the rows field, to seperate the calcuations click the expand all down one heirachy.

For the formula DATEDIFF you wan tot use MINX and MAXX instead.  instead of
DATEDIFF ( MIN(Table[OrderComplete]), TODAY (), DAY ),

DATEDIFF ( MINX(Table,Table[OrderComplete].[Date]), TODAY (), DAY )  Italicized part may not be needed.

Let me know if this helps.

[Boycie92]

Hi @cthurston

Thanks for getting back to me.

I dont actually want to see each individual order for the customer. I want to see the high level figure of all orders for that one customer.

Can I ask how this solves the problem were my max value is 12/31/9999?  but i want it to find the second highets value?

Thanks for the help,

Boycie92

[cthurston]

by high level figure do you mean the aggregate? the min? the max? the average?  (if aggregate the formula woudl still work.)

Is the 12/31/9999 a defualt value?   if I would cahnge them to nulls.