Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Excluding friday between two date range

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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)))

     

6 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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)))