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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
EliNoe
New Member

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

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. 

EliNoe_1-1738768163098.png

 

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

EliNoe_2-1738768442556.png

 

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

EliNoe_3-1738769578592.png

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

Revenus

Client FinalGLAccountOriginContractTypeDateValue
Client14011 MDRForecastRenewal2025-07-010
Client14011 MDRExistingNew contract2025-07-0116539
Client14011 MDRForecastRenewal2025-08-010
Client14011 MDRExistingNew contract2025-08-0116539
Client14011 MDRForecastRenewal2025-09-010
Client14011 MDRExistingNew contract2025-09-0116539
Client14011 MDRForecastRenewal2025-10-0113410.4
Client14011 MDRExistingNew contract2025-10-010
Client14011 MDRForecastRenewal2025-11-0113410.4
Client14011 MDRExistingNew contract2025-11-010
Client14011 MDRForecastRenewal2025-12-0113410.4
Client14011 MDRExistingNew contract2025-12-010
Client24011 MDRForecastUpsell (existing product)2025-07-011000
Client24011 MDRForecastRenewal2025-07-010
Client24011 MDRExistingNew contract2025-07-019050
Client24011 MDRForecastRenewal2025-08-017240
Client24011 MDRForecastUpsell (existing product)2025-08-011000
Client24011 MDRExistingNew contract2025-08-010
Client24011 MDRForecastUpsell (existing product)2025-09-011000
Client24011 MDRForecastRenewal2025-09-017240
Client24011 MDRExistingNew contract2025-09-010
Client24011 MDRForecastUpsell (existing product)2025-10-011000
Client24011 MDRForecastRenewal2025-10-017240
Client24011 MDRExistingNew contract2025-10-010
Client24011 MDRForecastUpsell (existing product)2025-11-01400
Client24011 MDRForecastRenewal2025-11-017240
Client24011 MDRExistingNew contract2025-11-010
Client24011 MDRForecastRenewal2025-12-017240
Client24011 MDRForecastUpsell (existing product)2025-12-01400
Client24011 MDRExistingNew contract2025-12-010
Client34011 MDRForecastRenewal2025-07-010
Client34011 MDRForecastRenewal2025-08-0112536
Client34011 MDRForecastRenewal2025-09-0112536
Client34011 MDRForecastRenewal2025-10-0112536
Client34011 MDRForecastRenewal2025-11-0112536
Client34011 MDRForecastRenewal2025-12-0112536

 

DateTable

DateKeyDateYearMonthMonth-YearMonthNumber
202507012025-07-012025JulJul-20257
202507022025-07-022025JulJul-20257
202507032025-07-032025JulJul-20257
...     
202512312025-12-312025DecDec-202512



 

5 REPLIES 5
Anonymous
Not applicable

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
Helper V
Helper V

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



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
Super User
Super User

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.

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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