Latest Enterprise DNA Initiatives

Subtract dates in the same column and return zero in case of the identical date values

Hello all,

I was looking for a way to create a calculated column that auto calculate the duration between dates in a date column called orderdate, with a filter of another column let’s call it Product.

I composed the following expression:

ByAymanProductFlowDays =

var currentproduct=Sales[ProductName]

var previousdate=MAXX(FILTER(Sales,Sales[ProductName]=currentproduct&&Sales[OrderDate]<EARLIER(Sales[OrderDate])),Sales[OrderDate])

var result=IF(ISBLANK(previousdate),0,Sales[OrderDate]-previousdate)

return result

When using this expression on adventureworks data I got the results as shown in the following image

In the small red circle there are two consecutive values of 1. My goal is to subtract the dates in the red column

so because the relevant dates are identical the yellow highlited value should be zero.

How to fix the DAX expression or rewrite it to meet my goals?

Than you all

Ayman

Hi @Ayman. Perhaps the techniques shown in this pattern or it’s subsequent discussion could help. Good luck. Greg

1 Like

Many thanks
Will try it.

Hi @Ayman, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!