cancel
Showing results for
Did you mean:
Frequent Visitor

## For every month in table 2, count the number of occurences current month is between Start & End date

Hi all,

I have two tables, Table 1:
New Calendar = (Year, monthyear, quarter year, empty column)

 2018 April, 2018 2018/Q2 2018 May, 2018 2018/Q2 2018 June, 2018 2018/Q2 2018 July, 2018 2018/Q3 2018 August, 2018 2018/Q3 2018 September, 2018 2018/Q3 2018 October, 2018 2018/Q4 2018 November, 2018 2018/Q4 2018 December, 2018 2018/Q4 2019 January, 2019 2019/Q1 2019 February, 2019 2019/Q1 2019 March, 2019 2019/Q1 2019 April, 2019 2019/Q2 2019 May, 2019 2019/Q2 2019 June, 2019 2019/Q2 2019 July, 2019 2019/Q3 2019 August, 2019 2019/Q3 2019 September, 2019 2019/Q3 2019 October, 2019 2019/Q4 2019 November, 2019 2019/Q4 2019 December, 2019 2019/Q4

And Table 2 : (ID, Start Date, End Date)

 2 2018-10-11 2018-12-15 3 2018-04-01 2018-12-15 6 2018-10-01 2018-11-30 7 2018-02-15 2018-04-15 9 2018-10-30 2018-12-15 10 2018-11-15 2019-03-30 11 2018-11-15 2019-03-30 12 2018-11-15 2019-02-28

Basically, for every month-year in table 1, I want to get the number of working days for that specific month. So I want to calculate the sum of working days found in table 2.
If I take as an example the month of december 2018, it would be:
11 working days for ID = 2
11 working days in ID = 3

11 working days in ID = 9

21 working days for ID = 10

Etc.

I tried using a measure instead of a column, but it didn't work:

```PlannedWorkingDays = SUMX(
FILTER('Table1,
'Table1'[END DATE]>MIN(Table2[YearMonthShort])
&&
'Table1'[START DATE]<MAX(Table2[YearMonthShort])
),
(DATEDIFF('Table1'[Start Date],'Table1'[END DATE],WEEK)*40)/DATEDIFF('Table1'[Start Date],'Table1'[END DATE],WEEK))```

But it doesn't work as october should have more than 40 days. (See attached picture)

Any help would be appreciated

Thanks
Ben

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: For every month in table 2, count the number of occurences current month is between Start &

Try this:

1. Create a calendar table (change the years to cover if necessary):

```CalendarTable =
VAR _FirstYear = 2018
VAR _LastYear = 2019
RETURN
CALENDAR ( DATE ( _FirstYear; 01; 01 ); DATE ( _LastYear; 12; 31 ) );
"DayType"; IF ( WEEKDAY ( [Date]; 2 ) IN { 6; 7 }; "WeekEnd"; "WorkingDay" )
)
```

2. Place Table1[yearmonth] in the axis of the chart as you already show

3. Create this measure and place it in values of the chart:

```Measure =
VAR _FirstDayMonth =
DATEVALUE (SELECTEDVALUE(Table1[monthyear]))
VAR _LastDayMonth =
EOMONTH ( _FirstDayMonth; 0 )
VAR _Result =   SUMX (
Table2;
VAR _FirstDayOp =
MAX ( _FirstDayMonth; Table2[Start Date] )
VAR _LastDayOp =
MIN ( _LastDayMonth; Table2[End Date] )
RETURN
CALCULATE (
COUNT ( CalendarTable[Date] );
CalendarTable[DayType] = "WorkingDay";
CalendarTable[Date] >= _FirstDayOp;
CalendarTable[Date] <= _LastDayOp
)
)
RETURN
_Result```

5 REPLIES 5
Super User

## Re: For every month in table 2, count the number of occurences current month is between Start &

Hi @Ben_RPA

Is the number of working days in December 2018 up to 2018-15-12 not 10 instead of 11??

So the result of the measure would be the sum of all these?

11 working days for ID = 2
11 working days in ID = 3

11 working days in ID = 9

21 working days for ID = 10

Etc.

i.e. go over all IDs in Table2 and count how many working days per ID fall into the month being evaluated and then sum them all up. Correct?

I think it'd be best to use a calendar table with working and weekend days

Frequent Visitor

## Re: For every month in table 2, count the number of occurences current month is between Start &

Hi @AlB ,

Thanks for the correction. It is 10 and not 11.

Is there a formula or function to get the number of working days per month/quarter?
I know it exists in Excel, not sure if it exists in Power BI.

Yes, my objective is exactly what you said:
go over all IDs in Table2 and count how many working days per ID fall into the month being evaluated and then sum them all up.

Thanks
Ben

Highlighted
Super User

## Re: For every month in table 2, count the number of occurences current month is between Start &

Try this:

1. Create a calendar table (change the years to cover if necessary):

```CalendarTable =
VAR _FirstYear = 2018
VAR _LastYear = 2019
RETURN
CALENDAR ( DATE ( _FirstYear; 01; 01 ); DATE ( _LastYear; 12; 31 ) );
"DayType"; IF ( WEEKDAY ( [Date]; 2 ) IN { 6; 7 }; "WeekEnd"; "WorkingDay" )
)
```

2. Place Table1[yearmonth] in the axis of the chart as you already show

3. Create this measure and place it in values of the chart:

```Measure =
VAR _FirstDayMonth =
DATEVALUE (SELECTEDVALUE(Table1[monthyear]))
VAR _LastDayMonth =
EOMONTH ( _FirstDayMonth; 0 )
VAR _Result =   SUMX (
Table2;
VAR _FirstDayOp =
MAX ( _FirstDayMonth; Table2[Start Date] )
VAR _LastDayOp =
MIN ( _LastDayMonth; Table2[End Date] )
RETURN
CALCULATE (
COUNT ( CalendarTable[Date] );
CalendarTable[DayType] = "WorkingDay";
CalendarTable[Date] >= _FirstDayOp;
CalendarTable[Date] <= _LastDayOp
)
)
RETURN
_Result```

Frequent Visitor

## Re: For every month in table 2, count the number of occurences current month is between Start &

Hi AIB,

First of all, I'd like to thank you. It worked!
I really appreciate that you took the time to help me with this matter.
I have a couple couple (I am sorry if they come up as idiotic questions, I am still learning how to use DAX/Power BI):

1) Why do you use "MAX" for FirstDayOp? Wouldn't the default solution to take the value of 'Table2'[Start Date] as it will always be greater or equal to FirstDayMonth? Same logic for the LastDayOp?

Couldn't we just set the variable to something like: "Var _FirstDayOp = SELECTVALUE(Table2'[Start Date])?

2) Does it matter (change the outcome) if the measure I create is in Table2 or Table1?

3) Solely based on the measure created in step 3, without any visualization/filter: The measure calculated the total work hours required per month, as long as it is within the range of the earliest Table2[Start Date] and the latest Table2[End Date]?

4) Any tips/recommendation for a good online DAX training course?

Thanks again,

Ben

Super User

## Re: For every month in table 2, count the number of occurences current month is between Start &

Cheers

1) Why do you use "MAX" for FirstDayOp? Wouldn't the default solution to take the value of 'Table2'[Start Date] as it will always be greater or equal to FirstDayMonth? Same logic for the LastDayOp?

Couldn't we just set the variable to something like: "Var _FirstDayOp = SELECTVALUE(Table2'[Start Date])?

Not really. To start with, there's no filter context active on Table2'[Start Date] at that stage so SELECTVALUE(Table2'[Start Date]) would throw an error. the MAX is necessary because we want to know whether we'll start counting days from the first day of the month at stake (when Table2'[Start Date] <_FirstDayMonth or from Table2'[Start Date] (when Table2'[Start Date] > _FirstDayMonth )

2) Does it matter (change the outcome) if the measure I create is in Table2 or Table1?

Not at all. Measures can be created anywhere. Usually they are created in the table they are most related to but that's mostly a matter of taste and how you want to organize them.  You can even create an empty table and keep all your measures there in order to have them all in one place.

3) Solely based on the measure created in step 3, without any visualization/filter: The measure calculated the total work hours required per month, as long as it is within the range of the earliest Table2[Start Date] and the latest Table2[End Date]?
I don't understand the question

4) Any tips/recommendation for a good online DAX training course?
I haven't done any but would guess anything from the SQLBI.com guys will be pretty good, given the top-notch quality of their materials. If you're interested in books, check my last posting on this thread

If you find any of my answers useful please consider kudoing them

Thanks again,

Ben