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.