cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

6 REPLIES 6
Community Support

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.

Helper I

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.

Community Support

Regards,

Jimmy Tao

Community Support

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.

Helper I

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.

Helper I

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors