Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Calculate date difference from two different tables and exclude the weekends

Hello all!  I'm still learning how to use Power BI, Currently im stuck in this problem.

I need to get the difference between two dates from 2 different tables and i need to exclude the weekends.

I used datediff but ofcourse i need to filter out the weekends.  I tried several solutions i found here in the community but still doesnt solve my problem. :(

 

 

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    dear my friend use this formula 

    date diffrence  = DATEDIFF(Review[Review date ].[Date],RELATED(Response[Response date]),DAY)
  • MattAllington's avatar
    MattAllington
    Community Champion
    8 years ago

    There are many ways to solve this. One way I like is to add a column in a calendar table that indicates week days vs weekends. You could put 1 for a week day and 0 for a weekend. 

     

    Read about calendar tables here https://exceleratorbi.com.au/power-pivot-calendar-tables/

     

    in this case the calendar table would not be joined to your data table. Read about that here https://powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/

     

    you can then write a measure that filters your calendar table and adds up the weekday column. 

     

    Working days = 

    var start = table[startdate]

    var end = table[enddate]

     

    Return calculate(sum(calendar[working days]),(filter(calendar, calendar[date] >= start && calendar[date]<=end))

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi i actually created a column which determines if that day is a weekday or weekend. 

       

       

      And then i created a column that relates to other table.

       

       

      Here is the calculated column i created:

       

      And here is the result: Im not getting the correct answer.  :(

       

       

      i tried the solution below but i cant declare the other table