cancel
Showing results for
Did you mean:
Frequent Visitor

## Cumulative total of measure to show in card

Hi all,

I need an little help with my DAX formula.

In short: I need a running total of a measure I have created. The measure I have created calculates the monthly fee for a specific date, month or year. But what I want to know is "what is the total fee from the first of March to the end of this month?"

In depth:
I have multiple accounts (salesforce) with a single value on it as monthly fee. It's a static value with no date or whatsoever.

 Accoutname Monthly fee Account1 100 Account2 120 Account3 110 Account4 105

To calculate the total monthly fee for each month, I created this measure which works fine (relationships are DateTable - opportunity and opportunity - account) This returns every date, month or year the same value as intended (435):

VAR _var1 =
CALCULATE(
SUM(Account[Monthly_fee__c]),
ALLEXCEPT(Opportunity,Opportunity[StartDate_invoice__c]))

The second part is to calculate the the running total of the monthly fee when the Opportunity [StartDate_invoice__c] has been reached.
So for example for Account1 has an StartDate_Invoice__c of the first of April and Account2 has an StartDate_Invoice__c of the first of May.
Result for April should be 100 and the result for May should be 220. The measure I've created for this is:

VAR _var2 =
CALCULATE(
SUM(Account[Monthly_fee__c]),
Opportunity[StartDate_invoice__c] < EOMONTH(TODAY(),0),
NOT(ISBLANK(Opportunity[StartDate_invoice__c])),
FILTER(ALLSELECTED(Dates),
Dates[Date] <= MAX(Dates[Date])))

The outcome for this works like a charm. If we do the calculation _var1 minus _var2 the result is this:
 Month Monthly fee March 435 April 335 May 215 June 215

The final point, referring to my question, I unable to do the cummulative calculation of the outcome of the measure. So I want to have a card which shows me the total earned fee to the end of this month.

With these numbers, it should be (435 + 335 + 215 + 215) = 1200

Who can help me fixing the last part?

1 ACCEPTED SOLUTION
Solution Supplier

Hi @SebJ ,

Try using this measure:
Fee(fixed)= SUMX(VALUE(Month), [VAR_var2])

2 REPLIES 2
Frequent Visitor

Yes! this works! Thanks.

The first measure (measure 1) which ends like:

Return

_var1 - _ var2

then I created a second measure with your solution

Measure 2 = SUMX(VALUES(Dates[YearMonth]), measure1)

Solution Supplier

Hi @SebJ ,

Try using this measure:
Fee(fixed)= SUMX(VALUE(Month), [VAR_var2])

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors