Forum Discussion

prakritnepal's avatar
prakritnepal
Icon for Helper I rankHelper I
7 years ago

Cumulative to date by id

HI, 

 

I am trying to see total change in pipeline value over time. The table setup for this is, date and opportunity stage table. The opportunity table has a lot of duplicate values with difference in dates. I need to grab the sum of amount for each id from the opportunity table which is <=each day on the date table. 

my current logic is:

 

Pipeline Value =

VAR maxdate = max('opp stage'[OpportunityHistories.CreatedDate])
VAR opp = values('opp stage'[OpportunityHistories.Id])


Return
Calculate([tot hist amt],
filter('Date',maxdate<'Date'[Date]),
filter('opp stage',opp))
 
 
 
 
tot hist amt = sum('opp stage'[OpportunityHistories.Amount])
 
I am not sure what I am doing wrong. Any help would be appreciated.
 
 
Need Help

6 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    prakritnepal ,

     

    To be general, you may modify your measure like below and check if it can work:

    Pipeline Value =
VAR maxdate =
    MAX ( 'opp stage'[OpportunityHistories.CreatedDate] )
VAR opp =
    VALUES ( 'opp stage'[OpportunityHistories.Id] )
RETURN
    CALCULATE (
        [tot hist amt],
        FILTER ( 'Date', maxdate < 'Date'[Date] ),
        FILTER ( ALLEXCEPT ( 'opp stage', 'opp stage'[Id] ), opp )
    )

    Community Support Team _ Jimmy Tao

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

    • prakritnepal's avatar
      prakritnepal
      Icon for Helper I rankHelper I
      7 years ago

      v-yuta-msft 

       

      That did not work. 

       

      Error: A table of multiple values was supplied where a single value was expected. 

      The date table and opp stage table are have many to one relationship on created date (opp stage) and date (date table) if that helps. 

       

      still struggling to store one value each in a variable for each id and sum that up. 

       

      • v-yuta-msft's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        prakritnepal ,

         

        Are the dataset very large? If not,  I would suggest you to merge the two tables then you can have same row content in the same merged table.

         

        Community Support Team _ Jimmy Tao

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