Net Work Days Grouped By Month

[Anonymous]

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?

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])

[Anonymous · Accepted solution]

File attached.

Best

D

[Anonymous]

// 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]

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

[Anonymous]

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

[Anonymous]

[Anonymous]

Dates must be dates, numbers must be numbers. Make sure you've got correct data types set up.

Best
D

[Anonymous]

My 'Day Type' field to denote working day is currently text (probably causing the error) - Should it be an integer flag?

[Anonymous]

[Net Work Days] =
// ProjectID can be anything but must be the identifier
var __oneProjectVisible = HASONEVALUE( 'Project'[ProjectID] )
// StartDate must be date
var __startDate = SELECTEDVALUE( Project[StartDate] )
// EndDate must be date
var __endDate = SELECTEDVALUE( Project[EndDate] )
var __result =
	CALCULATE(
		COUNTROWS( 'Calendar' ),
		// Calendar[Date] must be date
		KEEPFILTERS( __startDate <= 'Calendar'[Date] ),
		KEEPFILTERS( 'Calendar'[Date] <= __endDate ),
		// Day Type must be text since I equate it
		// to a text vaule
		KEEPFILTERS( 'Calendar'[Day Type] = "Working Day" )
	)
return
	if( __oneProjectVisible, __result )

Best

D

[Anonymous]

Thank you - got it so it does not throw an error but it's returning BLANK cells. Do I need to create a relationship from the DATE table to my project table?

[Anonymous]

This is what I put in the measure originally at the head:

// 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.

Best
D

[Anonymous]

Yes - I saw it (thought it may be a typo) - guess I'm not understanding how the measure operates - should it be placed in the matrix as a value? or elsewhere?

[Anonymous]

Drag your project name on the matrix. Drag the months on the matrix. And drag the measure on the matrix. Measure will always be in the Values field. You can't put it anywhere else.

Best
D

[Anonymous]

Here's the solution. Notice that the date slicer is dynamic and shows only those units of time that do have at least one project running inside them.

Best

D

[Anonymous]

Thank you so much - it's working perfectly. I do have another layer of complexity though. I need to multiply the returned date by a percentage. I thought I could just reference the measure in the calc (i.e., networkdays * [column with %]) which works, but doesn't incorporate the month aspect. Is there a way to update the measure to reference the column I need to multiply by?

[Anonymous]

I don't understand how you can multiply a date by a number... It's not possible.

Best
D

[Anonymous]

Sorry, I was not clear - the below highlights what I need to do ( right now, the output is not taking into consideration the month)

[Percentage] * [Net Work Day Measure] 

Project	Percentage	Net work Day (March)	Output (March)	Net work Day (April)	Output (april)
Project 1	50%	9	4.5	15	7.5
Project 2	20%	22	4.4	20	5

[Anonymous]

Sure it's possible but I'd create a second measure for this and name it appropriately. If the measure must work on the month level only, you have to ensure this by checking whether the current context contains full months only and then multiply the measure by the percentage iterating through Projects.

Best
D

[Anonymous]

Thank you - would you happen to know the best way to do it? Sorry for the follow-up, if there's documentation that you'd recommend that would be appreciated.

Create a new measure ->[Network Day Measure] * [Percentage column?]

[Anonymous · Accepted solution]

File attached.

Best

D

[Anonymous]

You're a saint - thank you so much for the help 😀

[Anonymous]

And one last thing.... Kudos would be much appreciated. That the button with the thumbs-up...

Best
D