Column with Number of working days in the month for each working day.

[Anonymous]

Dear all,

I have a Table Calendar'[Date] with dates from 01.01.2009 until 31.12.2022. This Table contain also a column IsWoringDay "Yes" or "No". I want to add additional column using DAX which gives me number of Working Days in a month for each working day. For Example: Januar 2009 has 22 Working days and 22 should be displayed in each row were IsWorkingDay = "Yes". Rows with IsWorkingDay = "No" should be empty. Do You have any Idea how to solve it?

Thank You in advance for any help!

[kcantor]

@Anonymous 

Your calculation would simply be WorkingDays = CALCULATE(DISTINCTCOUNT(Date[DateKey]), Date[IsWorkingDay] = "Yes")

You can then wrap that into an if statement  of Working Days in Month = IF(Date[IsWorkingDay] ="Yes", [WorkingDays], "")

Depending on your column names and table names this could be changed.  My question is why would you want to return a blank? Generally when I create that type of information I use it at a month level anyway to showing 22 working days in January would not need to be seen at a row level below the month. In addition, I would create that as a measure instead of a column and use it in a calculation or to display at a higher granularity than a day level.

If you can provide more information on how this will be used, we can provide additional guidance.

[Anonymous]

@kcantor Thank You for Your reply.

Unfortunately, I get only "1" in each field and not "22" (working days).

I have two tables. 

1. Excel table with values defined for each month.

2. Calculatd table with date in format. 01.01.2019,  02.01.2019 ... 31.12.2019

I want to get avarage amount for each working day (amount in a Month/working days). This should be visualated in a Matrix Table for each day in the month and comparied with other working day values. I have joined this two tables via "CROSSJOIN" function.