Forum Discussion
Net Work Days Grouped By Month
Hi
I am trying to create a matrix vizual that allows me to view a number of values by month, one of them being the # of net work days between a projects start and end date, but with grouped by months (please see example)
| March | April | May | |
Project1 | # of Network days for project 1 in march | # of Network days for project 1 in April | # of Network days for project 1 in May |
| Project2 | # of Network days for project 2 in march | # of Network days for project 2 in April | # of Network days for project 2 in May
|
I currently have the following measure which uses the projects start and end date - how can i revise the code to bucket the number of work days into it's associated month?
- Anonymous6 years ago
File attached.
Best
D
- Anonymous6 years ago
OK. Here's one possible solution. Please read the description in the file and play around with the data to understand what's going on.
Best
D
34 Replies
- AnonymousNot applicable
// First, you should have a Calendar // in your model that covers all the // years found in any of the date fields // in any of your tables. This Calendar // should be disconnected from the Project // dimension. // Second, this Calendar should have all // the date/time entities defined like // months, years, day names, and each // date should have a column indicator // to say if it's a working day or not. // Project dimension stores Projects, // that is, their ID's, names and start // and end dates. // Then you can write this measure: [Net Work Days] = var __oneProjectVisible = HASONEVALUE( 'Project'[ProjectID] ) var __startDate = SELECTEDVALUE( Project[StartDate] ) var __endDate = SELECTEDVALUE( Project[EndDate] ) var __result = CALCULATE( COUNTROWS( 'Calendar' ), KEEPFILTERS( __startDate <= 'Calendar'[Date] ), KEEPFILTERS( 'Calendar'[Date] <= __endDate ), KEEPFILTERS( 'Calendar'[Day Type] = "Working Day" ) ) return if( __oneProjectVisible, __result )This measure returns the number of working days in the currently selected period of time for any one project that's been selected. If more than one project is visible, it'll return BLANK since you've not defined what it means "the number of working days if more than 1 project is selected." It might be that you want the number of working days that belong to any of the projects (which would be reasonable) but if that's the case, you'll need to make some changes to the code.
Best
D
- AnonymousNot applicable
Thanks for your reply - I have all of the components mentioned, though, when I try to add the measure to the 'Value' box in the matrix, I get the following:
- AnonymousNot applicable
- AnonymousNot applicableDates must be dates, numbers must be numbers. Make sure you've got correct data types set up.
Best
D
- AnonymousNot applicable
Also, the matrix will have multiple projects (loaded onto the 'Rows' pill) - how can I accomodate?
- AnonymousNot applicableIf you've got only one project visible in your matrix/table in each row... you don't have to do anything. If there are multiple projects visible in the current context, then BLANK will be returned.
Best
D