Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
hemal_kanjia
Helper I
Helper I

Calculate No. of working days in month

Hello all,

 

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

 

DateDayWorking Day
01-11-2017Wednesday25
02-11-2017Thursday24
03-11-2017Friday23
04-11-2017Saturday22
05-11-2017Sunday22
06-11-2017Monday21
07-11-2017Tuesday20
08-11-2017Wednesday19
09-11-2017Thursday18
10-11-2017Friday17
11-11-2017Saturday16
12-11-2017Sunday16
13-11-2017Monday15
14-11-2017Tuesday14
15-11-2017Wednesday13
16-11-2017Thursday12
17-11-2017Friday11
18-11-2017Saturday10
19-11-2017Sunday10
20-11-2017Monday9
21-11-2017Tuesday8
22-11-2017Wednesday7
23-11-2017Thursday6
24-11-2017Friday5
25-11-2017Saturday4
26-11-2017Sunday4
27-11-2017Monday3
28-11-2017Tuesday2
29-11-2017Wednesday1
30-11-2017Thursday0

 

 

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.

2 ACCEPTED SOLUTIONS

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

Regards
Zubair

Please try my custom visuals

View solution in original post

@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.

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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.

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

Regards
Zubair

Please try my custom visuals

@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.

@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.

 

image.png      ========>    image.png

 

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

 

image.png   ========>   image.png

 

 

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.