cumulative totals for some projects that have started and some that haven't

[mrothschild]

BI File: https://www.dropbox.com/s/rbjoqspdjdmtquq/Power%20BI%20Forum%20Sample.pbix?dl=0

Excel Data: https://www.dropbox.com/s/i95ssp7bjxcglzt/Portfolio%20Tool%20-%20Standardized%20Multi%20Asset%20Mode...

I am setting up BI for projects, some of which start in the future and some of which have already started.  The data supporting this is essentially flattened, which I'm recognizing is a problem, but haven't had the time to figure out how to pivot it to work nicely with BI and my understanding of the new programming.

So what I'm trying to achieve, using the table below as a very simplified example, is to return a figure of 27,500, which would be the current debt of projects that have started, plus the starting debt of projects that haven't yet started.

Again, my data is flattened, so the "GROUPBY" or "SUMMARIZE" is necessary and I've used them, but only really via cut/paste to make them work rather than a deep understanding of what they're actually doing.  

	Project Start	Starting Debt	Current Debt
	10/31/2018	9000	8500
	3/31/2019	15000	0
	6/30/2019	4000	0
TOTAL		28000	8500

[moumipanja · Accepted solution]

1. Create a measure which will sum up the current debt of projects that have started

Measure1 = CALCULATE(SUM('Sheet2 (2)'[Current Debt]),FILTER('Sheet2 (2)','Sheet2 (2)'[Project Start]<=TODAY()))

2. Create a measure which will sum up the starting debt of projects that have not yet started

Measure2 = CALCULATE(SUM('Sheet2 (2)'[Starting Debt]),FILTER('Sheet2 (2)','Sheet2 (2)'[Project Start]>TODAY()))

3. Create another measure that will sum up the above two measures and display the result in a Card 

Measure = [Measure1]+[Measure2]

You will the following result

[mrothschild]

Thanks for your help - because my data is flattened your solution didn't quite work exactly, but it got me where I needed to be.  I needed to add a filter to a calculate so the formula looks like this which is essentially 3 measures:  (A) total starting debt outstanding per project, (B) starting debt outstanding for projects that have already started and (C) Current outstanding debt for projects that have already started.  

The outcome is (A) - (B) + (C).  So in the example from the table I provided earlier would be 28,000 - 9,000 + 8,500 = 27,500 to get to the same outcome.

Debt Outstanding on Deployed and Pipeline Deals =

CALCULATE(

SUMX(

GROUPBY('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Asset ID],'_Bronn Portfolio Analysis Table'[Debt Outstanding]),

'_Bronn Portfolio Analysis Table'[Debt Outstanding]

),

FILTER('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Count]=0)

)

-

CALCULATE(

SUMX(

GROUPBY('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Asset ID],'_Bronn Portfolio Analysis Table'[Debt Outstanding]),

'_Bronn Portfolio Analysis Table'[Debt Outstanding]

),

FILTER('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Count]=0),

FILTER('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Lease Start Date]<today())

)

+

SUMX (

FILTER (

'_Bronn Portfolio Analysis Table',

DATE ( YEAR ( '_Bronn Portfolio Analysis Table'[Calendar Month] ), MONTH ( '_Bronn Portfolio Analysis Table'[Calendar Month] ), 1 )

= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )

),

'_Bronn Portfolio Analysis Table'[Debt Outstanding]

)