Forum Discussion

Boycie92's avatar
Boycie92
Resolver I
8 years ago
Solved

Date diff based on first date and last date

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(T...
Need Help
Show and Tell
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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's avatar
Boycie92
Resolver I
8 years ago

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's avatar
    Boycie92
    Resolver I
    8 years ago

    Hi,

     

    Does anyone else know how to fix this?

     

    Thanks,

    Boycie92