Forum Discussion

CCWhatsup's avatar
CCWhatsup
New Member
7 years ago

Help with DATEDIFF?

Hi,

 

I have a visual with 2 dates from different tables that I need to get the difference between in order to get actual response times for our calls.

 

Column 1 is:  Job[Job_Created_Date]

Column 2 is:  JobResourceWorkTime[Work_time_start]

 

Column 2 has been filtered on the visual by earliest as I dont want all of the time logs from JobResourceWorkTime and get multiple entries for each job, I just want the first one.

Column 2 has also been filtered by JobresourceWorkTime[Work_time_type] = "working time" otherwise travel times would show up as well and show when an engineer had started their travel to a call instead of when they arrived.

 

Is there a way that I can recreate these filters as a part of the same formula as a DATEDIFF?

 

Thanks in advance.

 

 

Need Help

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    Hello CCWhatsup 

    Give this a try.

    Days =
DATEDIFF (
    SELECTEDVALUE ( Job[Job_Created_Date] ),
    CALCULATE (
        MIN ( JobResourceWorkTime[Work_time_start] ),
        JobresourceWorkTime[Work_time_type] = "working time"
    ),
    DAY
)

     

    • CCWhatsup's avatar
      CCWhatsup
      New Member
      7 years ago

      Thanks for the reply. This seems to generate blank results in the column that it creates. No errors though.

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        CCWhatsup 

        Are you trying to add this as a calculated column or in a card visual or matrix?  Could you share your .pbix file and show the location where you are trying to add the value?  It will help us to give you the right answer.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI CCWhatsup ,

    I'd like to suggest you write measure for dynamic calculation result, current power bi not support to create dynamic calculated column/table based on filter/slicer.

    If you confused on coding formula, please share some sample data and expected result for test.

    Regards,
    Xiaoxin Sheng