cancel
Showing results for
Did you mean:
mrothschild Member

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
moumipanja Regular Visitor

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

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 2 REPLIES 2
moumipanja Regular Visitor

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

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 Member

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

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

Announcements #### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. #### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work Top Kudoed Authors
Users Online
Currently online: 114 members 1,396 guests
Recent signins:
• gg_10 • vivianloi • annamalaig • velusekar • wenhuaiqing • Meena • Carnivean • matpj • ajkriz • yahookevin • kiryakozaj • shruthsr • ibtissam13 • rafael_aviles 