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

[Ayman]

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

[Greg]

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

Previous Row Value

Often in a Power BI visual one wants to get the value from the previous row to use in a calculation in the current row (e.g., to see if there’s a change between the previous value and the current value). The Previous Row Value DAX pattern can be of help when faced with this issue. As with most situations in Power BI, there are many ways to solve the same problem: here are a couple: Method 1: Add DAX Index Column 1-Add a [DAX Index] calculated column to your table DAX Index = // DAX PATTERN …

[Ayman]

Many thanks
Will try it.

[EnterpriseDNA]

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!

[EnterpriseDNA]

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!