Forum Discussion

bryn987's avatar
bryn987
Helper I
3 years ago
Solved

Datesbetween with filter excluding weekends

I know this has been asked many times but I can't exactly get the proposed methods to work in my situation

 

I have 2 tables, sales and date table and this formula to create a new column but getting a dax comparison error

 

Anyone have a better formual to use?

 

 

 

AVG Resolved Days = calculate(count(date[Date]), filter(date, date[IsWorkingDay]= "True"),datesbetween(date[Date], 'sales'[Created]+1, 'sales'[Resolved]))

 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi bryn987 ，

    Please try this:

    AVG Resolved Days = 
VAR _date1 = 'Sales'[Created] + 1
VAR _date2 = 'Sales'[Resolved]
VAR _count =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Date'[Date] ),
            FILTER (
                ALL ( 'Date' ),
                'Date'[IsWorkingDay] = TRUE ()
                    && 'Date'[Date] >= _date1
                    && 'Date'[Date] <= _date2
            )
        )
    )
RETURN
    _count

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • bryn987's avatar
    bryn987
    Helper I
    3 years ago

    Anonymous 

     

    I need to add in a 2nd condition for a holiday flag.  I tried this to no avail.

     

    Any ideas?

     

    AVG Resolved Days = 
VAR _date1 = 'Sales'[Created] + 1
VAR _date2 = 'Sales'[Resolved]
VAR _count =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Date'[Date] ),
            FILTER (
                ALL ( 'Date' ),
                'Date'[IsWorkingDay] = 1 && 'Date'[IsHoliday] = 1
                    && 'Date'[Date] >= _date1
                    && 'Date'[Date] <= _date2
            )
        )
    )
RETURN
    _count