Number of working days in each month

[yugofukuda]

Hi, 
I would like to put # of working days in each month like this image (considering holidays)  but i don´t know how to do that...

I would be appreciated if you could help me!! Thanks.

[AllisonKennedy]

@yugofukuda

What you are looking for is a cumulative sum or running total by month, so you just need to have a rank or index column to indicate the order in which to sum the values, for this example you can use Date column. Then just check that the Month of the values to SUM is the same as the month of the current row where you are going to put the answer. This can be done in a calculated column using the EARLIER function. Since you have already accounted for holidays with your IsWorkingDay column and since it is a numerical field with 1 for working day and 0 for non working, you can use SUMX on the IsWorkingDay column:
RunningTotalWorkDays = SUMX(FILTER(Calendar, Calendar[Month] = EARLIER(Calendar[Month]) && Calendar[Date]<= EARLIER(Calendar[Date])), Calendar[IsWorkingDay])

Note this is similar to the measure provided by @amitchandak but with fewer filter conditions that I don't believe are necessary for your table and also with including the Table Name when referencing columns, which while not required in a calculated column is still good practice.

Please let us know how you get on and if you have any questions!

[amitchandak]

@yugofukuda , Try like

sumx(filter(Calendar, [month]=earlier[Month] && [isworkingday]=earlier([isworkingday]) && [isworkingday]=1 && [Date] <=earlier([Date])),[isworkingday])

[yugofukuda]

@amitchandak @AllisonKennedy 

Thank you very much for your help!!

But it didn´t work. Whas should I do??
if it need, i don´t mind if column of "isWorkingday" is deleted (it´s not necessary).

[mahoneypat · Accepted solution]

Please try this expression

WorkingDayNumber =
VAR thisdate = 'Date'[Date]
VAR thismonth = 'Date'[Month]
RETURN
IF (
'Date'[IsWorkingDay] = 1,
CALCULATE (
SUM ( 'Date'[IsWorkingDay] ),
'Date'[Month] = thismonth,
'Date'[Date] <= thisdate
)
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[yugofukuda]

@mahoneypat 

Thank you very much!!! but it didn´t work. 

The message in Power BI is "A circular dependency was detected: Calendar[IsWorkingDay], Calendar[WorkingDayNumber], Calendar[IsWorkingDay]".  

[mahoneypat]

That's weird.  Worked in my calendar table.  What is the expression used in your IsWorkingDay column?  Mine is 

IsWorkingDay = if(WEEKDAY('Calendar'[Date]) in {1,7}, 0,1)

Regards,

Pat

[yugofukuda]

@mahoneypat 

Thank you very much for your quick response. 
Mine is  "IsWorkingDay = IF (NOT('Calendar'[dayname]= "Saturday"|| ('Calendar'[dayname]= "Sunday"))&& COUNTX(RELATEDTABLE('Holidays'),1)<1,1,0)" 


When i tried your expression, it worked well but I would like to consider holidays. holidays table is as below. 

[mahoneypat]

Please try this one instead that avoids the CALCULATE.  Replace 'Date' with 'Calendar' throughout.

WorkingDayNumber =
VAR thisdate = 'Date'[Date]
VAR thismonth = 'Date'[YearMonthnumber]
RETURN
    IF (
        'Date'[IsWorkingDay] = 1,
        COUNTX (
            FILTER (
                'Date',
                'Date'[Date] <= thisdate
                    && 'Date'[YearMonthnumber] = thismonth
                    && 'Date'[IsWorkingDay] = 1
            ),
            1
        )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[yugofukuda]

@mahoneypat 

mahoneypat 

Hi, I made a column for holidays and It worked well by using your expression. Thank you very much!!

[yugofukuda]

@mahoneypat 
Thank you very much but it didn´t work well...

I guess my expression in iswokingday (considering Holidays) isn´t good.  in your case, it works well??