Date difference measure in DAX

[Anonymous]

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

[Pascal_KTeam]

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]

@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.

[amitchandak]

@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

Blog has instructions : https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

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

[v-lionel-msft · Accepted solution]

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]

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

[Anonymous]

instead of

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

try

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