Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I
Helper I

Cumulative to date by id



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

Calculate([tot hist amt],
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.
Community Support
Community Support

@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] )
        [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.



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. 


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



Jimmy Tao

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



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. 

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


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.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.