Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
Hi @Anonymous
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
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
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
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
@Anonymous
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 question4) 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 threadIf you find any of my answers useful please consider kudoing them
Thanks again,
Ben
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |