Forum Discussion

Boycie92's avatar
Boycie92
Icon for Resolver I rankResolver 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

Boycie92's avatar
Boycie92
Icon for Resolver I rankResolver I
8 years ago

Hi Anonymous

 

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

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.