Forum Discussion
DateDiff showing wrong value
Hello - Using the calculated column below, my datediff number if not giving me the correct values.
The order below shipped on June 17. The lead for that eventually generated an order was created on May 4. There should be 44 days between these two dates. But my calculation is producing the number 59.
It is also producing a value for orders that I know have not shipped yet. For example, the first order on the list (above the selected one) says 48 days. This is the wrong number of days but also that order has not even shipped yet.
Not knowing what the relationship is between your two tables, you could try this expression
Date Diff = VAR __orderdate = MIN ( SalesLifecycle[LeadCreateDate] ) VAR __thisorder = MIN ( SalesLifecycle[Order] ) VAR __shippeddate = CALCULATE ( MIN ( Shipments[Date Shipped] ), ALLSELECTED ( Shipments ), Shipments[Order] = __thisorder ) RETURN DATEDIFF ( __orderdate, __shippeddate, DAY )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
4 Replies
Anonymous - What happens if you just use this:
Datediff = (MAX(Shipments[Date Shipped]) - MIN(SalesLifecycle[LeadCreateDate]) ) * 1.Not knowing what the relationship is between your two tables, you could try this expression
Date Diff = VAR __orderdate = MIN ( SalesLifecycle[LeadCreateDate] ) VAR __thisorder = MIN ( SalesLifecycle[Order] ) VAR __shippeddate = CALCULATE ( MIN ( Shipments[Date Shipped] ), ALLSELECTED ( Shipments ), Shipments[Order] = __thisorder ) RETURN DATEDIFF ( __orderdate, __shippeddate, DAY )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi Anonymous ,
You could create two new measures to show "FIRSTDATE(SalesLifecycle[LeadCreateDate])" and "LASTDATE(Shipments[Date Shipped])". Then check if it returns the right date and use MAX/MIN to get correct date.
