Cumulative to date by id

[prakritnepal]

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.

[v-yuta-msft]

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

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

@prakritnepal ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft]

@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.

[prakritnepal]

@v-yuta-msft 

I dont think we can create a same table unless we are summarizing columns from different table. As there are multiple date and stages for each opportunity id, we need to grab the most recent one based on selection. 

I tried creating a table using summarize but that process did not work either. 

[prakritnepal]

I am trying the following formula for a summarized table:

temp table =

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

VAR amt = calculate(values('opp stage'[OpportunityHistories.Amount]),filter('opp stage',maxdate &&

values('opp stage'[OpportunityHistories.OpportunityId])))

Return

SUMMARIZECOLUMNS('opp stage'[OpportunityHistories.OpportunityId],"max date", maxdate,

"Amount", amt)

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

What I am trying to do here is:

for max date selected by user, this table should retrieve unique opportunity id, the max date associated with it and the amount associated with max date and opportunity id.

I am not sure how I can grab that information.