Calculating Cumulative Sum that works with certain data fields

[Anonymous]

EDIT: Formula is working, issue is rather arrising since not all [Sales Type] values are available on all the years.
Here is an example of the issue:
You can see that sales type highlighted here in orange is not shown on all years after it first occur (see FY26) and this is what needs fixing somehow.

Hi, 
I`m trying to develop a calculation that returns the correct cumulative sum of field [Sales] on a stacked bar chart. 
Details are: 
(see in comments sample data)
Y-axis: [Sales] from view_metrics table 
X-Axis: [Year of first payment]   from view_scenarios table
Filter: [Sales year] from view_metrics table
Legend: [Sales Type] from view_scenarios table

The current formula I have, and works for all apart from when I add the legend is: 

Cumulative Sales =

CALCULATE (

SUM(view_metrics[Sales]),

FILTER (

ALLSELECTED( viewmetrics[Sales Year] ),

view_metrics[Sales Year]<= MAX ( view_metrics[Sales Year] )

),

ALLSELECTED( view_scenario[Year of first payment].[Year]),

view_scenario[Year of first payment]<= MAX ( view_scenario[Year of first payment] )

)

The formula works perfectly until I add the legend [Sales Type].


Any suggestions are welcomed, many thanks.

[FreemanZ]

hi @Anonymous 

How are viewmetrics and view_scenario related?

[Anonymous]

Hey @FreemanZ 
Yes they are through [sales id] columns, many (view_metrics) to one (view_scenario) relationship.
Cross filter direction: Both

[FreemanZ]

hi @Anonymous 

could you paste some sample data?

[Anonymous]

Would something like this help?

View_Metrics
Sales ID	Sales Year	Sales
1	2023	10
1	2024	10
1	2025	20
1	2026	30
1	2027	40
1	2028	10
1	2029	20
2	2025	40
2	2026	10
2	2027	30
2	2028	50
2	2029	100
2	2030	30
3	2025	10
3	2026	30
3	2027	40
3	2028	10
4	2025	30
4	2026	10
4	2027	40

View_Scenario
Sales ID	Year of first payment	Sales Type
1	2022	Direct
2	2023	Card
3	2023	Direct
4	2024	Card

[Greg_Deckler]

@Anonymous In response to your PM, I believe what you are looking for is below. PBIX file is attached below signature.

Measure = 
    VAR __IDs = DISTINCT('View_Metrics'[Sales ID])
    VAR __Year = MAX('View_Metrics'[Sales Year])
    VAR __Table = FILTER(ALL('View_Metrics'), [Sales ID] IN __IDs && [Sales Year] <= __Year)
    VAR __Result = SUMX( __Table, [Sales])
RETURN
    __Result

[Anonymous]

Thanks @Greg_Deckler for your response. 

This measure is not calculating the cumulative sum for me against [Year of first payment] on x-axis, but does work perfectly fine for the [Sales Year] on x-axis instead.
I downloaded the PBIX as well and I don`t think it`s working on there too. 

I have figured out that my issue isn`t in the measure itself but rather that some of the sales types are not present every year (please see example added to main body), and perhaps what is required is to adjust for that. 

[Anonymous]

@FreemanZ Hi there, are you able to provide any further advice?