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!
