Excluding friday between two date range

[Anonymous]

@Anonymous @Anonymous 

Hi, I have been scratching my heads since many days. I have a file with three coloums,

SR_CREATION_DATE,Last_Update_Date, SR_Status

My problem is I need to calculate the date difference between based on below condition

TAT1 = IF([SR_Status]="Closed",DATEDIFF([SR_CREATION_DATE], [Last_Update_Date],DAY),DATEDIFF([SR_CREATION_DATE], TODAY(),DAY))

 So if the Sr_status shows closed, it should calculate the difference between SR_CREATION_DATE and Last_Update_Date and exclude friday or else if the Sr_status is open it should calculate the difference between SR_CREATION_DATE and Current date, and exclude friday.

[edhans]

If you have a Date table, this is relatively easy. This will work as a calculated column:

ExcludingFridays = 
VAR varDateList =
    FILTER(
        'Date',
        'Date'[Date] >= [Date1] && 'Date'[Date] <= [Date2] && 'Date'[Day Name] <> "Friday"
    )
RETURN
    COUNTROWS(varDateList)

my PBIX is here if you want to take a look at it.

[Anonymous]

the data i am working on is flowing through our database. My source is MySql. Data is related to Service Request and the status is either closed or open . Condition I am looking for is something like

if Sr_status is closed then Start_Date-END_Date minuss fridays in between

else Start_Date-Today() minus fridays in between.

[Anonymous · Accepted solution]

Hi @Anonymous 

Following @edhans logic, if you don't have a Date table, create a virtual one

TAT1 = 
VAR Dates=ADDCOLUMNS(CALENDAR(MIN('Table'[SR_CREATION_DATE]),TODAY()),"weekday",WEEKDAY([Date]))
RETURN
IF([SR_Status]="Closed",
    COUNTROWS(FILTER(Dates,[Date] >= [SR_CREATION_DATE] && [Date] < [Last_Update_Date] && [weekday] <> 5)),
    COUNTROWS(FILTER(Dates,[Date] >= [SR_CREATION_DATE] && [Date] < TODAY() && [weekday] <> 5)))

[Anonymous]

thanks, this almost worked buh only problem is difference between below dates is coming as blanks, is it possible to show as 1

19-Dec-2021 3:04:46 and 20-Dec-2021 12:00:00am

[Anonymous]

thanks @Anonymous Created a new coloumn to using int function to extract the date and rest is your formulae. thanks a lot.

[edhans]

Hi @Anonymous , While @Anonymous does work without a date table, I provided a date table in the example I shared, and did so for a reason. if you have any dates in your model, you need a data table. Best practice. Power BI DAX simply becomes a mess if you do too much without a date table, as can be seen by the length of the DAX code I provided and the one where you have to create a virtual date table. It compounds if you do that to many columns or measures.