Using DateDiff to find days between two dates in the same column using another column as a filter.

[tsuggs1]

Hi all,

I am having issues with finding the days between two dates that are in the same column.

Basically the table has a column called 'actualTime' that has a date and another column called code that has an event code (Delivered, CustomsCleared, Invoiced, etc.). The codes are all linked to a Plan_OID that is identical for several of the codes (i.e. Plan_OID 11111 may have a row that has the code Delivered and then another row with the same Plan_OID for a different code). I want to find the difference between two different code's 'actualTime'. 

I can generate a conditional column that essentially filters out the 'actualTime' into seperate columns for the specific event codes I am trying to use, but because they are in different rows (even though they have the same plan_oid) it is not finding the dates between. I am not super advanced with DAX, but I am wondering if there is another way to use DateDiff and filter the dates in the same column to find what I am looking for? 

Any help is greatly appreciated! Here is a screenshot to give an idea for the table I am using.

[Greg_Deckler]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[tsuggs1]

Hi Greg,

I read over your article however I don't think what I am trying to accomplish is quite the same or complex. Your formula for Repair Hours = DATEDIFF(Repairs[RepairStarted], Repairs[RepairCompleted],SECOND)/3600 is closer to what I am looking for, but these dates are in separate columns whereas in my table the issue is the two dates are not separated into separate columns, and I don't know how to generate a calculated column that would take the 'actualTime' for a specific code and generate this date for all of the same 'Plan_OID' entries in the table if that makes sense.

Maybe one of the other formulas could be broken down to do that, am I missing something?

[v-lid-msft]

Hi @tsuggs1 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[tsuggs1]

@v-lid-msft So now I am trying to exclude the weekends in my DateDiff calculation. I am having trouble finding a solution in past posts that allow for this when the dates are in two different tables.

Any advice?

[v-lid-msft]

Hi @tsuggs1 ,

Could you please share the formula of your DateDiff Calculation if it does not contain any confidential information so we can modify it? Please don't have any Confidential Information or Real data in your reply.

Best regards,

[tsuggs1]

@v-lid-msft Days Between = DATEDIFF('Table1'[actualTime - Customs Cleared].[Date], RELATED('Table1(2)'[actualTime -Delivered].[Date]),DAY)

[v-lid-msft]

Hi @tsuggs1 ,

We can use the following measure to meet your requirement:

Workday Count =
COUNTROWS (
    FILTER (
        CALENDAR (
            'Table1'[actualTime - Customs Cleared].[Date],
            RELATED ( 'Table1(2)'[actualTime -Delivered].[Date] )
        ),
        NOT ( WEEKDAY ( [Date] ) IN { 1, 7 } )
    )
)

Best regards,

[tsuggs1]

It is not allowing me to use:

Table1'[actualTime - Customs Cleared].[Date]

 &

'Table1(2)'[actualTime -Delivered].[Date]

as a measure. 

When I try creating it as a column I am getting an error that 'the start or end date in Calendar function can not be a Blank value'.

Note there are some columns where one of these dates may be null. I would also like to keep the days negative or postive based on if the end date is before the start date in some instances, as these 'days between' are then determining whether these items are "on time" based on another measure.

Let me know if you need anything else from me or if you can provide any other guidance!

[tsuggs1]

Here is the solution I ended up using:
https://community.powerbi.com/t5/Desktop/Trying-to-find-a-way-to-exclude-weekends-using-DateDiff-wit...

[tsuggs1 · Accepted solution]

Hi,

Thanks for the response.

I essentially found a solution using the same concept however I did it in a much simpler way using the GUI built into Power BI. I duplicated the table and filtered the original by the code I wanted. Then I filtered the duplicated table to the secondary code I needed. After that I created a relationship between the two tables and was able to pull the dates into my DATEDIFF formula to produce the number of days between. Everything seems to be working great!

Thanks again.

[v-lid-msft]

Hi @tsuggs1 ,

We can create two spreate table as the slicers and create a measure to meet your requirement:

Code1 = DISTINCT('Table'[code])

Code2 = DISTINCT('Table'[code])

Between Days =
ABS (
    DATEDIFF (
        CALCULATE (
            MAX ( 'Table'[actualTime] ),
            'Table'[code] IN FILTERS ( Code1[code] )
        ),
        CALCULATE (
            MAX ( 'Table'[actualTime] ),
            'Table'[code] IN FILTERS ( Code2[code] )
        ),
        SECOND
    ) / 3600 / 24
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

Best regards,