Date Difference issue

[Anonymous]

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 )

[amitchandak]

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

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

[Anonymous]

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

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

[Anonymous]

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?