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

[EliNoe]

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

[Anonymous]

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

[nicolast29]

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()

    )

)

[EliNoe]

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.

[johnt75]

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.

[EliNoe]

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.