Calculate the Number of Working Days Left in Current Month

[Anonymous]

Hi All, 

I have a calendar table where I'm using a calculated column to calculate the number of working days in each month. 

I need a formula to calculate the no. of working days left in the current month and to return the no. of working days in all the other months.

So for example, in March, I have a total of 21 working days and based on today's date, I should have 3 working days left. I need this formula to return 3 for March and the total no. of working days for Jan, Feb, April and so on. 

Thanks!

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could use COUNTROWS() function to calculate the rest working days.

Current Month =
IF (
    FORMAT ( TODAY (), "mmmm" ) = SELECTEDVALUE ( 'Table'[Column] ),
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Date] >= TODAY ()
                && NOT ( WEEKDAY ( 'Table'[Date], 1 ) IN { 1, 7 } )
        )
    ),
    [Working Day]
)

Here is the result.

Here is my test file for your reference.

[Anonymous]

Hi @v-eachen-msft , 

Your solution works perfectly and it was exactly what I was looking for. Thanks for the help!

[amitchandak]

If you have to work date

remaining Days = sumx(filter(Date,Date[Date]>= today() && Date[Date]<= eomonth(today(),0)),Date[Working Days])

[Anonymous]

Hi @amitchandak , 

Your formula returns the no. of working days left in the current left, but I also need to it to return the no. of days left in all the other months. For March, it correctly returns 3 and I need it to return 21 for January, 20 for Feb, 20 for April...

Please see attached screenshot. 

[amitchandak]

Try

if(month(today()) = month(Date[Date]),
sumx(filter(Date,Date[Date]>= today() && Date[Date]<= eomonth(today(),0)),Date[Working Days]),
sumx(Date,Date[Working Days]))

[Anonymous]

Hi @amitchandak , 

If I use a measure, IF statement won't let me reference my date column in my calendar table and if I use a calculated column I get the following result, 

[camargos88]

Hi @Anonymous ,

Try this measures:

WorkingDays = CALCULATE(COUNT('Date'[Date]); WEEKDAY('Date'[Date]; 1) in {2;3;4;5;6})

WorkingDaysLeft = CALCULATE(COUNT('Date'[Date]); FILTER('Date'; WEEKDAY('Date'[Date]; 1) in {2;3;4;5;6} && 'Date'[Date] >= TODAY() ))

Ricardo