Help with DATEDIFF?

[CCWhatsup]

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.

[Anonymous]

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

[jdbuchanan71]

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]

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

[jdbuchanan71]

@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.