Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 : Create...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

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