Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Date Difference issue

Hello  -  I am having a hard time getting the right result.    First, I do have a date table.     Second, I am not using the DATEDIFF formula as I've tried using it but it does not allow for if the start date is larger than the end date.    This can occur because I am comparing a Due Date, with an Actual Ship Date...sometimes the actual ship comes before the due date.

 

I have two criteria that must be met:    No weekends can be counted.   And the Date Shipped should have a buffer of +3 days over the Due Date (but again, not counting weekends).      So, in the example below ,4 weekdays elapsed and this should say "Late". 

 

 

In this example below, this PO  (which had 4 lines) shipped on Feb 17th.     In this case, there was a weekend in between, and the count of weekdays is correct  (3)  but my calculation is returning  "Late"  instead of  "On Time".   

 

 

 

AR Channel OnTime or Late = if('Flu Shipped'[Date Shipped] - 'Flu Shipped'[Due Date]<=3, "On Time","Late")

 

 

Date Difference Measure =
VAR DueDate =
    MIN ( 'Flu Shipped'[Due Date] )
VAR DateShipped =
    MIN ( 'Flu Shipped'[Date Shipped] )
VAR CalendarNoWeekends =
    FILTER (
        DateTable,
        DateTable[Date] >= DueDate
            && DateTable[Date] <= DateShipped
            && DateTable[Weekend or Weekday] = "Weekday"
    )
RETURN
    COUNTROWS ( CalendarNoWeekends )

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous I guess you are not using datediffmeasure in R Channel OnTime or Late calculation. try using date diff measure 

    AR Channel OnTime or Late = if(Date Difference Measure <=3, "On Time","Late")

    Or else here is another approach

    1. Create a weekday column in date table

    Weekday = WEEKDAY('Calendar'[Date],2)

    2. Finally create a measure 

    Measure = 
VAR _due = SELECTEDVALUE('Table'[Due Date])
VAR _shipped = SELECTEDVALUE('Table'[Date Shipped])
VAR _datediff = CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar','Calendar'[Date]>=_due&&'Calendar'[Date]<=_shipped&&'Calendar'[Weekday]<6))
RETURN IF(_datediff<=3,"On Time","late")
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Vimal   -   Your new options worked well.  

       

      The only issue I am seeing is that in a table visual, when I have that measure applied on the table, it seems to ignore any date filter.  

       

      For example, I have the Page filtered to only show 2020 orders....but regardless of that the table visual shows me orders from all years.     If I remove the measure from the table, I get my filtered results back again to just 2020 orders.   

       

      Any idea why this is happening?  

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous  -   Hi Vimal   -   The measure works but the one problem I have with it is that it ignores any filter context I have.  

       

      For example, if I apply your measure to my table visual, it does not cross filter when I use my chart visual to filter by month.    You can see the difference here.     

       

       

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Refer, if these can help

    https://www.youtube.com/watch?v=bs3yzmf9elA

    https://community.powerbi.com/t5/Desktop/Date-Difference-excluding-weekends/td-p/426776

    https://community.powerbi.com/t5/Desktop/Calculate-Days-between-Two-Dates-excluding-weekends-different/td-p/407984

     

    If these do not help

     

    Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
    Proud to be a Datanaut My Recent Blog -
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970