Running Total with multiple entries per date

[BFroc]

Hey Everyone,

I've been trying to build a dashboard that connects to our GIS (ESRI ArcGIS 10.3) and our budgeting software (TeamBudget) and I'm trying to calculate the total inventory vs budget annually from 1990. I have inventory data going back to 1900, and budget data back to 1990. I've found several solutions that do running totals using the following syntax:

Running Total in Length Plan = CALCULATE(
				Sum(WD_MAIN_MV_VW[LENGTH_PLAN]),
				All(WD_MAIN_MV_VW),
				WD_MAIN_MV_VW[YEAR_CONSTRUCTED] <= EARLIER (TableDates[Date],1))

However, as cities are not built one project at a time, I have several segments being added to our inventory, independently, every year. Our old data just uses January 1st of each year as we don't have as-built information on the exact construction completion date. 

Here's a snapshot of our dataset for our inventory table (WD_MAIN_MV_VW) as edited by my query.

 I'm thinking I may have to calculate twice? Year to date summaries for every year as one measure, then another measure for life to date?

[GilbertQ]

Hi @BFroc

What about this measure?

Running Total in Length Plan = 
CALCULATE (
    SUM ( 'WD_MAIN_MV_VW'[LENGHT_PLAN] ),
    FILTER (
        ALL ( 'TableDates'[Date] ),
        'TableDates'[Date] <= MAX ( 'WD_MAIN_MV_VW'[YEAR_CONSTRUCTED] )
    )
)

[v-huizhn-msft]

Hi @BFroc,

For your requirement, how to create a life to date summaries for every year as one measure? In your resource table data, I can't find life to data field? Could you please share more details for further analysis?

Best Regards,
Angelia

[BFroc · Accepted solution]

Hi @v-huizhn-msft

I would use the year to date summary for each individual year, and sum that over the years in the lifespan I want to look at.

I can create this.

Life to date - 54000
2014 - 27000
Project 1 - 15000
Project 2 - 12000
2015 - 27000
Project 3 - 7000
Project 4 - 20000

I want create this.

2014 - 27000
Project 1 - 15000
Project 2 - 12000
2015 - 54000
Project 3 - 7000
Project 4 - 20000

I can do the first bit easily. It's showing those values as a running total that I have trouble doing... I think the April update may have solved my issues.

[v-huizhn-msft]

Hi @BFroc,

You have resolved your issue? Please mark right reply as answer. Other persons will find solution easily. Thanks for understanding.

Best Regards,
Angelia