Measure to calculate sum but include rows based on dates

[kmistry]

Hi, hopefully will be able to do a good job of explaining this:

Context

I am making a gantt chart for a set of projects which will show a person's total capacity across a number of projects. A person can either be a project lead or an additional resource 1,2,3 etc. If a person is a project lead, they are assigned "50" capacity for each project. If they are an additional resource, they are assigned 10. I have a measure "Total Capacity" which adds up all the capacity for a person and displays it in a table. I also have a gantt chart in the form of a matrix which is working correctly and showing what I want.

Issue

My issue is arising when I try to filter by a date which is BEFORE a projects end date. E.g in the screenshot below, I have filtered for 28/04/2023 - 30/09/2023 which is BEFORE the end date of Client 1b (31/10/2023). The matrix shows all 3 clients correctly, but the bottom table is only accounting for client 1a and 2b, because the end dates are prior to 30/09/2023. Client 1b is ignored. 

Solution

I would like the total capacity for "John Smith" to be 110 (50 for client 1b and 1a, 10 for client 2b). Currently, it shows 60 because it is ignoring client 1b. Ideally, the bottom table would also show client 1b.

Thank you in advance. Please see attached sample file. 

Sample File 

[v-yueyunzh-msft · Accepted solution]

Hi , @kmistry 

Thanks for your sample pbix file first!

According to your description, you want to show the according Resource in your Matric visual.

I download your pbix file and see the relationship for your tabls are using the [Date] to [endDate]. This is why your table visual show only two resources.

For your needs, you want to use slicers to display custom resources, so you cannot create an active relationship.

Then i modify your [Column Values] measure as the judgement.

Column Values = 

VAR _startDate = 
CALCULATE( 
    MIN('Resource Forecasting'[Resources -- Start Date (Actual)]), 
    ALL('Date'))
VAR _endDate = 
CALCULATE( 
    MIN('Resource Forecasting'[Resources -- ETA Go-live (current tracking)]), 
    ALL('Date'))

VAR _period = MIN('Date'[Date]) >= _startDate && MAX('Date'[Date]) <= _endDate
RETURN 
//IF(_period, 1)
_period

We can create two measures like this:

Measure = var _t =  ADDCOLUMNS( CROSSJOIN( VALUES('Resource Forecasting'[Title]) , VALUES('Date'[Year-Month])) ,  "flag" ,IF( [Column Values] =FALSE(),0,1)   )

return
IF(SUMX(_t,[flag])>0 ,1,-1)

Total Capacity-2 = var _t =  ADDCOLUMNS( CROSSJOIN( VALUES('Resource Forecasting'[Title]) , VALUES('Date'[Year-Month])) ,  "flag" ,IF( [Column Values] =FALSE(),0,1)   )
var _t2=ADDCOLUMNS(FILTER( GROUPBY(_t , [Title] , "sum_flag" , SUMX(CURRENTGROUP(),[flag])),[sum_flag]>0) , "total" , [Total Capacity])
return
sumx(_t2,[total])

Then we can put the mesure on the "Filter on this visual" for your table visual:

And if your want to get the right total in your above table visual , we may use the [Total Capacity-2] measure in it:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[kmistry]

Thank you Aniya, this was exactly what I was looking for!