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

     

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    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.