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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Boycie92
    Resolver 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.