topic Re: Need help with DAX measure to calculate the sum of the NEW renewal revenue each month in DAX Commands and Tips

Need help with DAX measure to calculate the sum of the NEW renewal revenue each month

EliNoe — Wed, 05 Feb 2025 15:56:18 GMT

Hello everyone,

I'm building a revenue forecast in Power Pivot using DAX (within Excel 365).

I need help with a DAX measure that I'm struggling with.

I tried to explain below as clearly as possible with sample data and expected result to help you understand my problem.

I would greatly appeciate any help with this!!

Thank you in advance for your time 🙂

_________________________________________________________________________

This is a visual of a forecasted revenue for Jul-Dec 25 as an example.

The goal: I need to calculate the sum of New Renewal revenus each month and sum it at the GL account level (4011 MDR in my example).

Result I'm looking for (in red):

The measure I need help with: The measure I tried only sums up properly at the GLAccount level for the first month of the year that has a Renewal value (with my example data, it sums properly in August, but shows 0 in October).

=var CurrentMonthRenewalValue= if(isblank( CALCULATE( SUM(Revenus[Value]), Revenus[ContractType] = "Renewal")),0, CALCULATE( SUM(Revenus[Value]), Revenus[ContractType] = "Renewal") ) var PreviousMonthRenewalValue= if(isblank( CALCULATE( SUM(Revenus[Value]), Revenus[ContractType] = "Renewal", DATEADD(DateTable[Date], -1, MONTH) ) ),0, CALCULATE( SUM(Revenus[Value]), Revenus[ContractType] = "Renewal", DATEADD(DateTable[Date], -1, MONTH) ) ) VAR IndividualResult = if( AND(PreviousMonthRenewalValue=0, CurrentMonthRenewalValue>0),CurrentMonthRenewalValue,0) RETURN sumx(VALUES(Revenus[GLAccount]), calculate(IndividualResult , ALLEXCEPT(Revenus,Revenus[GLAccount])) )

I only have 2 tables in my data model (relationship one-to-many on Date):

Here is the data if you want to try it out:

Revenus

Client Final	GLAccount	Origin	ContractType	Date	Value
Client1	4011 MDR	Forecast	Renewal	2025-07-01	0
Client1	4011 MDR	Existing	New contract	2025-07-01	16539
Client1	4011 MDR	Forecast	Renewal	2025-08-01	0
Client1	4011 MDR	Existing	New contract	2025-08-01	16539
Client1	4011 MDR	Forecast	Renewal	2025-09-01	0
Client1	4011 MDR	Existing	New contract	2025-09-01	16539
Client1	4011 MDR	Forecast	Renewal	2025-10-01	13410.4
Client1	4011 MDR	Existing	New contract	2025-10-01	0
Client1	4011 MDR	Forecast	Renewal	2025-11-01	13410.4
Client1	4011 MDR	Existing	New contract	2025-11-01	0
Client1	4011 MDR	Forecast	Renewal	2025-12-01	13410.4
Client1	4011 MDR	Existing	New contract	2025-12-01	0
Client2	4011 MDR	Forecast	Upsell (existing product)	2025-07-01	1000
Client2	4011 MDR	Forecast	Renewal	2025-07-01	0
Client2	4011 MDR	Existing	New contract	2025-07-01	9050
Client2	4011 MDR	Forecast	Renewal	2025-08-01	7240
Client2	4011 MDR	Forecast	Upsell (existing product)	2025-08-01	1000
Client2	4011 MDR	Existing	New contract	2025-08-01	0
Client2	4011 MDR	Forecast	Upsell (existing product)	2025-09-01	1000
Client2	4011 MDR	Forecast	Renewal	2025-09-01	7240
Client2	4011 MDR	Existing	New contract	2025-09-01	0
Client2	4011 MDR	Forecast	Upsell (existing product)	2025-10-01	1000
Client2	4011 MDR	Forecast	Renewal	2025-10-01	7240
Client2	4011 MDR	Existing	New contract	2025-10-01	0
Client2	4011 MDR	Forecast	Upsell (existing product)	2025-11-01	400
Client2	4011 MDR	Forecast	Renewal	2025-11-01	7240
Client2	4011 MDR	Existing	New contract	2025-11-01	0
Client2	4011 MDR	Forecast	Renewal	2025-12-01	7240
Client2	4011 MDR	Forecast	Upsell (existing product)	2025-12-01	400
Client2	4011 MDR	Existing	New contract	2025-12-01	0
Client3	4011 MDR	Forecast	Renewal	2025-07-01	0
Client3	4011 MDR	Forecast	Renewal	2025-08-01	12536
Client3	4011 MDR	Forecast	Renewal	2025-09-01	12536
Client3	4011 MDR	Forecast	Renewal	2025-10-01	12536
Client3	4011 MDR	Forecast	Renewal	2025-11-01	12536
Client3	4011 MDR	Forecast	Renewal	2025-12-01	12536

DateTable

DateKey	Date	Year	Month	Month-Year	MonthNumber
20250701	2025-07-01	2025	Jul	Jul-2025	7
20250702	2025-07-02	2025	Jul	Jul-2025	7
20250703	2025-07-03	2025	Jul	Jul-2025	7
...
20251231	2025-12-31	2025	Dec	Dec-2025	12

Re: Need help with DAX measure to calculate the sum of the NEW renewal revenue each month

johnt75 — Thu, 06 Feb 2025 09:38:14 GMT

The problem is that variables in DAX aren't really variable, they are constants. They are only ever calculated once and then their value does not change, so trying to recalculate a variable with a different filter context won't work.

You can rewrite the code like

Total = SUMX ( VALUES ( Revenus[GLAccount] ), CALCULATE ( VAR CurrentMonthRenewalValue = CALCULATE ( SUM ( Revenus[Value] ), Revenus[ContractType] = "Renewal" ) VAR PreviousMonthRenewalValue = CALCULATE ( SUM ( Revenus[Value] ), Revenus[ContractType] = "Renewal", DATEADD ( DateTable[Date], -1, MONTH ) ) VAR IndividualResult = IF ( AND ( PreviousMonthRenewalValue = 0, CurrentMonthRenewalValue > 0 ), CurrentMonthRenewalValue, 0 ) RETURN IndividualResult, ALLEXCEPT ( Revenus, Revenus[GLAccount] ) ) )

I also stripped out the checks for blank, as blank equates to 0 so they are unnecessary.

Re: Need help with DAX measure to calculate the sum of the NEW renewal revenue each month

nicolast29 — Thu, 06 Feb 2025 13:17:46 GMT

hello
Pour le fun une variante 😋

Renewal3 =

IF(

ISINSCOPE(Revenue[GLAccount]) &&

NOT(ISINSCOPE(Revenue[Client Final])) &&

NOT(ISINSCOPE(Revenue[ContractType])),

-- Calcul pour le niveau GLAccount

CALCULATE(SUM(Revenue[Value]), REMOVEFILTERS(Revenue[Client Final], Revenue[ContractType]), Revenue[ContractType] = "Renewal"),

IF(

ISINSCOPE(Revenue[ContractType])&& MAX(Revenue[ContractType])="Renewal",

-- Calcul pour le niveau ContractType

IF(CALCULATE(SUM(Revenue[Value])=0),"",CALCULATE(SUM(Revenue[Value]))),

-- Sinon, afficher BLANK()

BLANK()

)

Re: Need help with DAX measure to calculate the sum of the NEW renewal revenue each month

Anonymous — Mon, 10 Feb 2025 01:15:39 GMT

Hi EliNoe

Has your problem been resolved? If so, could you mark the corresponding reply as the solution so that others with similar issues can benefit from it?

Best Regards,

Jayleny

Re: Need help with DAX measure to calculate the sum of the NEW renewal revenue each month

EliNoe — Mon, 10 Feb 2025 15:58:59 GMT

hello!

merci pour cette variante! bien apprécié 😉

Unfortunatly and maybe because I'm in Power pivot in Excel 365 and not in PowerBI, the ISINSCOPE function is not recognized by my version of DAX.

Re: Need help with DAX measure to calculate the sum of the NEW renewal revenue each month

EliNoe — Mon, 10 Feb 2025 16:36:26 GMT

Hi johnt75, thank you for your time and explanations!

I tried the revised code. Unfortunatly, it always return 0 for some reason 😞

I was able to make it work using a workaround (I added a calculated column in my Excel rawdata and used Excel to find the first renewal amount for each month). Then I'm using Dax to create a measure that sums this new calculated column at the GL account level and this works fine. I would be really curious to know how to achieve this with DAX only without having to do my workaround though.