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

     

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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