Cumulative Total by Person & Date, but EXCLUDING Sales Type

[Anonymous]

I am working on creating a commissions calculator in BI desktop.  Each sales rep has a sales goal that if they reach their commission rate will go up.  I need to create a cumulative total by sales person and date that ignores opportunity type to track progress against goal. 

I have a DAX formula that gives me the correct cumulative amount by person and date but does not return the correct amount when I drop Type in.  What am I missing in this code?

Cumulative Amount = 
CALCULATE(
    SUM(Opportunity[Amount]),
    FILTER(
        ALL(Opportunity[Document_Date__c]),Opportunity[Document_Date__c] <= MAX(Opportunity[Document_Date__c])),
        Opportunity[StageName] = "Closed Won"
)

Name	Document_Date__c	Opportunity Type	Amount	Cumulative Amount	SHOULD BE
Bob	1/2/2019 0:00	New	13,700.00	13,700.00	13,700.00
Bob	1/2/2019 0:00	Renewal	4,500.00	4,500.00	4,500.00
Bob	1/7/2019 0:00	New	43,680.00	57,380.00	61,880.00
Bob	1/17/2019 0:00	New	7,000.00	64,380.00	68,880.00
Bob	1/22/2019 0:00	New	14,100.00	78,480.00	82,980.00
Amanda	1/8/2019 0:00	Renewal	13,350.00	13,350.00	13,350.00
Mark	1/2/2019 0:00	New	63,524.00	63,524.00	63,524.00
Mark	1/11/2019 0:00	Renewal	8,800.00	8,800.00	72,324.00
Lindsay	1/4/2019 0:00	New	32,074.00	32,074.01	32,074.00
Lindsay	1/11/2019 0:00	New	-	32,074.01	32,074.00
Mike	1/4/2019 0:00	Upsell	100.00	100.00	100.00

[v-cherch-msft]

Hi @Anonymous

You may try below measure. If it is not your case,please share your sample data which could reproduce your scenario for us to test.You can also upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Cumulative Amount =
CALCULATE (
    SUM ( Opportunity[Amount] ),
    FILTER (
        ALL ( Opportunity ),
        Opportunity[Document_Date__c] <= MAX ( Opportunity[Document_Date__c] )
            && Opportunity[Name] = MAX ( Opportunity[Name] )
    ),
    Opportunity[StageName] = "Closed Won"
)

Regards,

Cherie

[v-cherch-msft]

Hi @Anonymous

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

Regards,

Cherie

[Greg_Deckler]

Probably need to replace your ALL with ALLEXCEPT(Opportunity[Document_Date__c],Opportunity[SalesPerson])

[Anonymous]

I am not able to get it to work with ALLEXCEPT.  I am pullin Name and Opportunity Type from seperate tables.