Calculate Number of Days for the Month excluding weekends

[atjt217]

Hello, 

Would like to ask for some help please? I have 2 issues:

1) Is there a way to modify this formula to not count weekends?

CurrentDays_in_month = DATEDIFF(EOMONTH(NOW(),-1),(NOW()-1),day)

What this does is it counts the number of days for the current month except same day (10/1/2020-10/13/2020) 

2) Is there a formula that can count the number of days of the month without the weekends that will not be affected by a filter? The formula on my first question does not get affected by filters. Is there a way to modify it to fit to this? 

[mahoneypat · Accepted solution]

Here is a measure expression that gives your desired result.

WD to Date This Month =
VAR __days =
    CALENDAR (
        EOMONTH (
            TODAY (),
            -1
        ) + 1,
        TODAY ()
    )
RETURN
    COUNTROWS (
        FILTER (
            __days,
            NOT (
                WEEKDAY ( [Date] )
                    IN {
                    1,
                    7
                }
            )
        )
    )

Regards,

Pat

[atjt217]

Actually I found the answer for number 2:

Count_Days_Month = DAY (

IF (

MONTH ( TODAY () ) = 12,

DATE ( YEAR ( TODAY () ) + 1, 1, 1 ),

DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 )

)

- 1

)

My only issue is how do i remove weekends on both of the calulations?

[mahoneypat · Accepted solution]

Here is a measure expression that gives your desired result.

WD to Date This Month =
VAR __days =
    CALENDAR (
        EOMONTH (
            TODAY (),
            -1
        ) + 1,
        TODAY ()
    )
RETURN
    COUNTROWS (
        FILTER (
            __days,
            NOT (
                WEEKDAY ( [Date] )
                    IN {
                    1,
                    7
                }
            )
        )
    )

Regards,

Pat

[atjt217]

Its exactly what im looking for @mahoneypat.Thanks for helping me out with the first question! I hope you can also help me with the other one please? 

[mahoneypat · Accepted solution]

Sure.  It is just a variation of the first one (just changed the 2nd term of the CALENDAR().

WD This Month =
VAR __days =
    CALENDAR (
        EOMONTH (
            TODAY (),
            -1
        ) + 1,
        EOMONTH (
            TODAY (),
            0
        )
    )
RETURN
    COUNTROWS (
        FILTER (
            __days,
            NOT (
                WEEKDAY ( [Date] )
                    IN {
                    1,
                    7
                }
            )
        )
    )

Regards,

Pat

[atjt217]

Thank you so much! 😃