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(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

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

6 Replies

  • 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

         

         

  • 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.