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]

That's very kind of you. I just asked for one... and got a whole lot. More than I expected. Many thanks, indeed.

Best
D

[Anonymous]

You know what? It would be good to define this measure(s) when several projects are visible in the current context. If you do this, then you won't have to check if only one project is visible and Totals in the matrix will work correctly and you'll be able to show them.

Best
D

[Anonymous]

I have two dimension on the rows of the matrix (the project name and people's names) - if there is only one person on the project, subtotals show up, but if there are multiple people, subtotals do not display and minimizing the project (using the +- icons) completely removes the project from the matrix.

[Anonymous]

As I said, you have to define what you want to see when here are several projects in scope. I can try to implement it but it's up to you to define it.

Best
D

[Anonymous]

Thank you - and forgive my naivety - Do you define this is in the measure itself or within the visual? I have multiple measures that I've created (with your help) - network days ,revenue, things like that - that I'd like to see the subtotals for.

[Anonymous]

First off, you have to know what your measure should return for the intersections of dimensions you're interested in. It's a matter of definition. Once you know what it means to "aggregate the measurement across the members of a dimension", you can start implementing this idea in the measure. Visuals don't implement anything. They just show you the result of the calculation which we simply call "a measure."

The easiest way to aggregate is to sum something. But summing is easy most of the time - you just sum across any dimension. But for balances of accounts it's harder because you cannot sum across time. The balance of an account in June is the last balance recorded in June. You cannot sum up the balances of the account because it would be meaningless. But you can sum across any other dimension, for instance, the dimension of customers.

You have to know what your [net work days] measure should return if there are 2+ projects visible in the current context. For one project you've defined this. But you have to tell what number you want to see for 2+ projects. And the measure must be corrected in such a way that it works for 1,2,... projects.

Best
D

[Anonymous]

Thank you for the granular answer - in most cases, I'd like aggregations to occur at the project leve (i.e., net work das for project 1  for april, etc). 

Your last point is where I'm confused. Is there a way for PBI to dynamically know how many projects are being shown within the visual at that point? How would you apply the subtotal calculation to the project level in the dimension?

[Anonymous]

Yes, DAX always knows what's visible in the current context.

By the ways, aggregation does not always mean "a sum." Aggregations can be (but not limited to): MIN, MAX, MEDIAN, QUANTILE, PERCENTILE, STANDARD DEVIATION, ABSOLUTE DEVIATION, COUNT, DISTINCT COUNT... Basically, an aggregation is anything that takes a set of rows and turns them into a value, text or numerical.

I'll try to expand the definition of your measure and will attach the file shortly.

Best
D

[Anonymous · Accepted solution]

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

[Anonymous]

Thank you so much. Oddly, it seems that my issue was required a much simpler (although probably much less efficient) solution. I wrapped my exisitng measures in a SUMX with the table reference being the project table. Would you happen to have a suggestion to learn more about proper measure creation?

[Anonymous]

Problem with your solution is that you'll be getting the sum of days (after you've multiplied the by percentages) and that leads to getting more days than there are in the selected period of time.

My solution ensures this will never happen unless the percentages summed over all the projects add up to something much greater than 100%.

There is no subject like a "proper measure creation." You can only say about measure optimization but that's a completely different thing. Your measure is always something that YOU think makes sense in your problem. Nothing more.

Best
D

[Anonymous]

Thank you for the explanation. I guess my confusion surrounds how to write the dax within the meausure itself. I.e, I have multiple measure on the values section of the matrix - does each value measure have to be updated to include an aggregation or is the aggregation done with a new measure?

[Anonymous]

Can you expand on this? I think I'll need to do this, as subtotals and grand total do not show most projects...

[Anonymous]

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: