Cumulative total of measure to show in card

Anonymous — Thu, 17 Jun 2021 09:31:42 GMT

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?

Thanks in advance!

Re: Cumulative total of measure to show in card

Tanushree_Kapse — Thu, 17 Jun 2021 09:59:49 GMT

Hi Anonymous ,

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

Re: Cumulative total of measure to show in card

Anonymous — Mon, 21 Jun 2021 09:32:45 GMT

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)

topic Re: Cumulative total of measure to show in card in DAX Commands and Tips

Cumulative total of measure to show in card

Re: Cumulative total of measure to show in card

Re: Cumulative total of measure to show in card