cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SebJ
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.

AccoutnameMonthly fee
Account1100
Account2120
Account3110
Account4105

 

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:
MonthMonthly fee
March435
April335
May215
June215

 

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?

 

Thanks in advance!

1 ACCEPTED SOLUTION
Tanushree_Kapse
Solution Supplier
Solution Supplier

Hi @SebJ ,

 

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

 

 

View solution in original post

2 REPLIES 2
SebJ
Frequent Visitor

Hi @Tanushree_Kapse ,

 

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)

Tanushree_Kapse
Solution Supplier
Solution Supplier

Hi @SebJ ,

 

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

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

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.