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 :

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
 

 

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.

7 Replies

    • 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

    instead of

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

    try

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.