Forum Discussion

tsuggs1's avatar
tsuggs1
Helper I
6 years ago
Solved

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

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 ...
Need Help
  • tsuggs1's avatar
    tsuggs1
    6 years ago

    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's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
tsuggs1
Helper I
6 years ago

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's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      tsuggs1
      Helper I
      6 years ago

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

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        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,