cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Date difference measure in DAX

Hi All,

I am calculating the difference in seconds between two dates in my data model. For calculation purpose I have converted two of my date columns from fact to measure using the below :

Created = if(HASONEVALUE('Fact'[Submitted]),values('Fact'[sys_created_on]),BLANK())
Closed = if(HASONEVALUE('Fact'[closed_at]),values('Fact'[closed_at]),BLANK())
Now I am trying to create a measure for no of seconds between the above two measures as :
Measure = CALCULATE(DATEDIFF([Resolved_Date],[Created],DAY),
filter(DimDate,DimDate[Weekday]="Weekday"))
But the issue is that I am the measure is returning a constant value of 43951 as seconds

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

Or do like this.

1. Cretae 4 calculated columns.

``````Weekday_sub = WEEKDAY([submitted], 2)
Weekday_res = WEEKDAY([resolved], 2)
Weeknum_sub = WEEKNUM( [submitted], 2 )
Weeknum_res = WEEKNUM( [resolved], 2 ) ``````

2. Create a measure.

``````Measure =
CALCULATE(
DATEDIFF(
MAX('Fact'[submitted]), MAX( 'Fact'[resolved]),
DAY
),
FILTER(
'Fact',
( 'Fact'[Weekday_sub] in {6, 7} || 'Fact'[Weekday_res] in {6, 7} ) &&
( 'Fact'[Weeknum_sub] = 'Fact'[Weeknum_res] ) &&
( ( 'Fact'[Weekday_res] in {6, 7} && 'Fact'[Weekday_sub] in {1, 2, 3, 4, 5} ) || ( 'Fact'[Weekday_sub] in {6, 7} && 'Fact'[Weekday_res] in {1, 2, 3, 4, 5}) )
)
)``````

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 REPLIES 7
Resolver I

Take a look at the blue card visual in this file https://www.dropbox.com/s/ww9063que0bcvrn/datediff%20in%20second.pbix?dl=0

If I understood your requirement correctly, this should hopefully help

Anonymous
Not applicable

@Pascal_KTeam  thanks but this does not solve my purpose. I have to calculate the duration in seconds based on certain conditions that I have mentioned in my earlier message.

Super User

@Anonymous , date dim is not joined to dates and date diff has no role there. so it is not working

Refer to this file how to get working day diff

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Anonymous
Not applicable

Maybe I was not clear with my requirements so, My fact table has two date columns submitted and resolved_at now I want to calculate the number of seconds between these two dates based on the conditions below

a. submitted and resolved on weekends

b.submitted and resolved in the same week

c. submitted on weekends and resolved on a weekday

d.submitted on a weekday and resolved on weekend

I have a date dimension that is connected to the fact on submitted and resolved_at dates.

thanks

Community Support

Hi @Anonymous ,

Or do like this.

1. Cretae 4 calculated columns.

``````Weekday_sub = WEEKDAY([submitted], 2)
Weekday_res = WEEKDAY([resolved], 2)
Weeknum_sub = WEEKNUM( [submitted], 2 )
Weeknum_res = WEEKNUM( [resolved], 2 ) ``````

2. Create a measure.

``````Measure =
CALCULATE(
DATEDIFF(
MAX('Fact'[submitted]), MAX( 'Fact'[resolved]),
DAY
),
FILTER(
'Fact',
( 'Fact'[Weekday_sub] in {6, 7} || 'Fact'[Weekday_res] in {6, 7} ) &&
( 'Fact'[Weeknum_sub] = 'Fact'[Weeknum_res] ) &&
( ( 'Fact'[Weekday_res] in {6, 7} && 'Fact'[Weekday_sub] in {1, 2, 3, 4, 5} ) || ( 'Fact'[Weekday_sub] in {6, 7} && 'Fact'[Weekday_res] in {1, 2, 3, 4, 5}) )
)
)``````

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks @v-lionel-msft , It worked like a charm

Anonymous
Not applicable

Measure = CALCULATE(DATEDIFF([Resolved_Date],[Created],DAY),

try

Measure = CALCULATE(DATEDIFF([Resolved_Date],[Created],SECOND),

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors