cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Calculating Date Difference between 2 or more dates using DAX

Hi. I'm new in to Power BI and I'm currently making a new column that would give me the latency(no. of days between each transaction) for every member (card number).  Our business rule defined the formula for latency as:

For Example: A member has an accumulated 3 visits:

 Visit3-Visit2 = Diff 1Visit2 -Visit1 =Diff 2Get average of all differences to get  value for latency

My data looks something like this:

Given that data set, what DAX formula should I use? What if a member would incur more than 3 visits? I came across a script online but I am not sure if it's correct but I gave it a try in Power BI but I encountered an error.

Tried it and I got this error:

I would really appreciate your help guys. I badly need this for a report presentation for my project. Thank you in advance!

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous,

The issue that you got error because the DateDiff() requires only three parameters passed. You can create the calculated column like below:

Diff = var  d=CALCULATE(MAX('Table1'[Transaction Date]),FILTER('Table1','Table1'[Card No.]=EARLIER(Table1[Card No.]) && 'Table1'[Transaction Date]<EARLIER(Table1[Transaction Date])))
return IF('Table1'[Transaction Date]<d,DATEDIFF('Table1'[Transaction Date],d,DAY),DATEDIFF(d,'Table1'[Transaction Date],DAY))

Then create a measure like below to return average of those difference values for each Card No.

Latency = CALCULATE(AVERAGE(Table1[Diff]),FILTER(ALL(Table1),'Table1'[Card No.]=MAX('Table1'[Card No.])))

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support

Hi @Anonymous,

The issue that you got error because the DateDiff() requires only three parameters passed. You can create the calculated column like below:

Diff = var  d=CALCULATE(MAX('Table1'[Transaction Date]),FILTER('Table1','Table1'[Card No.]=EARLIER(Table1[Card No.]) && 'Table1'[Transaction Date]<EARLIER(Table1[Transaction Date])))
return IF('Table1'[Transaction Date]<d,DATEDIFF('Table1'[Transaction Date],d,DAY),DATEDIFF(d,'Table1'[Transaction Date],DAY))

Then create a measure like below to return average of those difference values for each Card No.

Latency = CALCULATE(AVERAGE(Table1[Diff]),FILTER(ALL(Table1),'Table1'[Card No.]=MAX('Table1'[Card No.])))

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Member

Thanks u, I have solved my problem.

Anonymous
Not applicable

Hi @v-qiuyu-msft, in relation to this solved problem, I am now trying to create a segmentation based on the members' latency and Average Ticket Value.

My segmentation looks something like this:

And I want to know how can I do it in DAX given the data set that I currently have.

Thank you!

Anonymous
Not applicable

Thank you! This worked for me.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors