Need Help - Cumulative Sum

[mareidraper]

I am pulling in Opportunity Products from Salesforce Object and trying to determin Year over Year New Business Portion.

Some Opportunities have 5 years of products while others only have 1 year.

I am trying to determine New Business for 2018 vs 2019 vs 2020, etc.

The trouble I am running into is when there is a null value in year 2019 let's say, my sub and grand totals are not accurate.

It has plagued me now for months. I have set up measures to capture what the Yearly Values are by filtering by billing date:

ARR - 2018 = IF(ISBLANK(SUMX(FILTER('Opportunity Product','Opportunity Product'[Billing_011918__c].[Year]=2018),'Opportunity Product'[MRR_HIDDEN__c])),0,SUMX(FILTER('Opportunity Product','Opportunity Product'[Billing_011918__c].[Year]=2018),'Opportunity Product'[MRR_HIDDEN__c]))

I have plugged the prior year value if the value is to be null:

ARR - 2019 = IF(ISBLANK(SUMX(FILTER('Opportunity Product','Opportunity Product'[Billing_011918__c].[Year]=2019),'Opportunity Product'[MRR_HIDDEN__c])),[ARR - 2018],SUMX(FILTER('Opportunity Product','Opportunity Product'[Billing_011918__c].[Year]=2019),'Opportunity Product'[MRR_HIDDEN__c]))

The second formula is not pulling the plugged valued into the subtotals/totals.

Thoughts?

Any assistance would be greatly appreciated.

Best,

Marei Draper

[v-yulgu-msft]

Hi @mareidraper,

I tested the above two measures on my side, both of them returned expected values.

The second formula is not pulling the plugged valued into the subtotals/totals.

What is the result you have got?

Please share some sample data of 'Opportunity Product' and describe your desired output and currently incorrect result with screenshots. If possible, you could directly share the .pbix file.

Regards,

Yuliana Gu