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
    Community 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