Forum Discussion

BFroc's avatar
BFroc
Frequent Visitor
9 years ago
Solved

Running Total with multiple entries per date

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?

 

 

Need Help
  • BFroc's avatar
    BFroc
    9 years ago
    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.

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      BFroc
      Frequent Visitor
      9 years ago
      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's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

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