The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
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
hello
Pour le fun une variante 😋
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.
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.
User | Count |
---|---|
17 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
9 | |
8 |