Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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)

 

 MarchAprilMay

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?

 

NetWorkDays =
VAR Calendar1 = CALENDAR(MAX('Project'[StartDate]),MAX('Project'[EndDate]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

 

 

 

 

34 Replies

  • Anonymous's avatar
    Anonymous
    Not 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

    • Anonymous's avatar
      Anonymous
      Not 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:

       

       
       
       
    • Anonymous's avatar
      Anonymous
      Not applicable

      • Anonymous's avatar
        Anonymous
        Not applicable
        Dates must be dates, numbers must be numbers. Make sure you've got correct data types set up.

        Best
        D
    • Anonymous's avatar
      Anonymous
      Not applicable

      Also, the matrix will have multiple projects (loaded onto the 'Rows' pill) - how can I accomodate?

      • Anonymous's avatar
        Anonymous
        Not applicable
        If 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