cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ben_RPA Frequent Visitor
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)

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

 

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

22018-10-112018-12-15
32018-04-012018-12-15
62018-10-012018-11-30
72018-02-152018-04-15
92018-10-302018-12-15
102018-11-152019-03-30
112018-11-152019-03-30
122018-11-152019-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)Temp_PowerBI_Issue_2019-0314_START&ENDDATE.PNG


Any help would be appreciated Smiley Happy

Thanks
Ben

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
    ADDCOLUMNS (
        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
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

Ben_RPA Frequent Visitor
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

Super User
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
    ADDCOLUMNS (
        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

   

Ben_RPA Frequent Visitor
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! Smiley Happy
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

 

Highlighted
Super User
Super User

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

@Ben_RPA 

 

Answers in red

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 Smiley Wink 

 

Thanks again,

Ben