Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Impactful Individual
Impactful Individual

Hi @Anonymous ,

 

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

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Impactful Individual
Impactful Individual

Hi @Anonymous ,

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors