Calculate No. of working days in month

[hemal_kanjia]

Hello all,

I have a date table something like this given below (3rd column is the measure which I want) :

Date	Day	Working Day
01-11-2017	Wednesday	25
02-11-2017	Thursday	24
03-11-2017	Friday	23
04-11-2017	Saturday	22
05-11-2017	Sunday	22
06-11-2017	Monday	21
07-11-2017	Tuesday	20
08-11-2017	Wednesday	19
09-11-2017	Thursday	18
10-11-2017	Friday	17
11-11-2017	Saturday	16
12-11-2017	Sunday	16
13-11-2017	Monday	15
14-11-2017	Tuesday	14
15-11-2017	Wednesday	13
16-11-2017	Thursday	12
17-11-2017	Friday	11
18-11-2017	Saturday	10
19-11-2017	Sunday	10
20-11-2017	Monday	9
21-11-2017	Tuesday	8
22-11-2017	Wednesday	7
23-11-2017	Thursday	6
24-11-2017	Friday	5
25-11-2017	Saturday	4
26-11-2017	Sunday	4
27-11-2017	Monday	3
28-11-2017	Tuesday	2
29-11-2017	Wednesday	1
30-11-2017	Thursday	0

I want to calculate Remaining Working Days for that particular month (3rd column) using DAX function (Measure), but I can't get the exact value.

All I want to do is,

If the User select the 2nd November,

No. of Working days = Remaining days in selected month (28) - Sundays(4) = 24

If the User select the 10th November,

No. of Working days = Remaining days in selected month (20) - Sundays(3) = 17

If the User select the 16th November,

No. of Working days = Remaining days in selected month (14) - Sundays(2) = 12

If the User select the 22nd November,

No. of Working days = Remaining days in selected month (8) - Sundays(1) = 7

If the User select the 28th November,

No. of Working days = Remaining days in selected month (2) - Sundays(0) = 2

I can't get the same value.

Can anyone help me to solve this ?

Thanks in advance.

[Zubair_Muhammad · Accepted solution]

HI @hemal_kanjia

Try this MEASURE

Working Days =
CALCULATE (
    COUNTROWS ( TableName ),
    FILTER (
        ALL ( TableName ),
        TableName[Date] > SELECTEDVALUE ( TableName[Date] )
            && MONTH ( TableName[Date] ) = MONTH ( SELECTEDVALUE ( TableName[Date] ) )
            && YEAR ( TableName[Date] ) = YEAR ( SELECTEDVALUE ( TableName[Date] ) )
            && TableName[Day] <> "Sunday"
    )
)

[Zubair_Muhammad]

@hemal_kanjia

Try this calculated column

Working Days =
CALCULATE (
    COUNTROWS ( TableName ),
    FILTER (
        ALL ( TableName ),
        TableName[Date] > EARLIER ( TableName[Date] )
            && MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
            && TableName[Day] <> "Sunday"
    )
)

[Zubair_Muhammad]

@hemal_kanjia

[hemal_kanjia]

Hello @Zubair_Muhammad,

Thank you for reply.

I have tried this thing, but can't get the right value.

May be I have not only one month's date, i have 3 year's date. i think that's why it is not giving me the right value.

One more thing, 

from the above formula, i have to create "Calculated Column", because I think we can't use "Earlier" function in measure and also I want to use this in another measure whose formula is Total Sales / Remaining Working Days.

I want the Remaining Working Days of that month. 
For example, if I select the date range of 1st November to 10th November,

then the answer should be 17 (20 days remaining - exclude sundays).

I want to create measure. 

Is it possible ?

Thank You.

[Zubair_Muhammad · Accepted solution]

HI @hemal_kanjia

Try this MEASURE

Working Days =
CALCULATE (
    COUNTROWS ( TableName ),
    FILTER (
        ALL ( TableName ),
        TableName[Date] > SELECTEDVALUE ( TableName[Date] )
            && MONTH ( TableName[Date] ) = MONTH ( SELECTEDVALUE ( TableName[Date] ) )
            && YEAR ( TableName[Date] ) = YEAR ( SELECTEDVALUE ( TableName[Date] ) )
            && TableName[Day] <> "Sunday"
    )
)

[hemal_kanjia · Accepted solution]

@Zubair_Muhammad

Thank You for giving your golden time to give the answer.

I have found the solution.

Here is the updated Formula:

Working Days =

CALCULATE (
COUNTROWS ( DateTable ),
FILTER (
ALL ( DateTable ),
(TableName[Date]) > MAX(TableName[Date])
&& TableName[Date].[MonthNo] = MONTH(MAX(TableName[Date]))
&& TableName[Date].[Year] = YEAR(MAX(TableName[Date]))
&& TableName[DayName] <> "Sunday"
)
)

Thank You.

[hemal_kanjia]

@Zubair_Muhammad 
Thank You !!!
It's working.

but when I select the range of dates, it gives me Blank Value.

For example, If I select 1st November, it gives 25. that is perfect.

      ========>    

But when I select 1st November to 5th November, it gives blank instead of 21.

   ========>   

I also tried to get Maximum date from selected date, however i got the blank value.

Working Days =
CALCULATE (
    COUNTROWS ( TableName ),
    FILTER (
        ALL ( TableName ),
        TableName[Date] > MAX ( TableName[Date] )
            && MONTH ( TableName[Date] ) = MONTH ( SELECTEDVALUE ( TableName[Date] ) )
            && YEAR ( TableName[Date] ) = YEAR ( SELECTEDVALUE ( TableName[Date] ) )
            && TableName[Day] <> "Sunday"
    )
)

Please see in the above formula at Underline. 
Please correct me if I put that at wrong place.

Thanks You.