Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

View all the Fabric Data Days sessions on demand. View schedule

Reply
Lodan
Helper II
Helper II

Numbers not matching

‎02-10-2025 07:21 AM

Hello,

 

I tfeels like I have a weird issue and I hope I am missing something simple.

 

So I have two measures.  The first calculates the revenue for each month as long as the start date is equal to or before the last day of the month and the end date is greater than the last day of the month.

 

The second is designed to capture forecast so needs to assume that the end date doesn't come.  So it has the end date fixed to the 31st Jan 2025.  My challenge is that when I look at January 2025 in the output this should then have exactly the same value in both.  But it doesn't:

 

Lodan_0-1739200676878.png

Of course if I change the date in Forecast MRR to 'MaxDate' then the numbers match.  I even verified that MAX was indeed looking at the 31st Jan for it.

 

 

MRR Revenue = 
VAR BaseTable = SUMMARIZE( 'GBP', 'Dates2'[Month Year], 'GBP'[Source], 'GBP'[Rate] )
VAR ConvertedValueGBP = SUMX(
	BaseTable,
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'GBP'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR] ),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'GBP'[Rate]
	RETURN Result
)
VAR BaseTable2 = SUMMARIZE( 'EUR', 'Dates2'[Month Year], 'EUR'[Source], 'EUR'[Rate] )
VAR ConvertedValueEUR = SUMX(
	BaseTable2,
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'EUR'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR]),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'EUR'[Rate]
	RETURN Result
)
VAR UnconvertedValue = SUMX( VALUES( Dates2[Month Year] ),
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Result = CALCULATE(
		SUM( Combined[MRR $] ),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		ISBLANK( Combined[Currency] ) 
	)
	RETURN Result
)
VAR Result = ConvertedValueGBP + UnconvertedValue + ConvertedValueEUR
RETURN Result

 

 

and:

 

 

Forecast MRR = 
VAR BaseTable = SUMMARIZE( 'GBP', 'Dates2'[Month Year], 'GBP'[Source], 'GBP'[Rate] )
VAR ConvertedValueGBP = SUMX(
	BaseTable,
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'GBP'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR] ),
		Combined[End Date] > date(2025,1,31), 
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'GBP'[Rate]
	RETURN Result
)
VAR BaseTable2 = SUMMARIZE( 'EUR', 'Dates2'[Month Year], 'EUR'[Source], 'EUR'[Rate] )
VAR ConvertedValueEUR = SUMX(
	BaseTable2,
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'EUR'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR]),
		Combined[End Date] > date(2025,1,31),
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'EUR'[Rate]
	RETURN Result
)
VAR UnconvertedValue = SUMX( VALUES( Dates2[Month Year] ),
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Result = CALCULATE(
		SUM( Combined[MRR $] ),
		Combined[End Date] > date(2025,1,31),
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		ISBLANK( Combined[Currency] ) 
	)
	RETURN Result
)
VAR Result = ConvertedValueGBP + UnconvertedValue + ConvertedValueEUR
RETURN Result

 

.  

 Do you have any thoughts or ideas please?

 

Thanks

Labels:
Message 1 of 5
331 Views
0
4 REPLIES 4
Lodan
Helper II
Helper II

‎02-10-2025 09:36 AM

Think I just got closer.  I think the random months working was due to it not knowing which months / year were greater than Jan 2025.  So i did the same but matched on a yearmonth formula column and now all looks good apart from Jan bizarely:

 

Forecast MRR = 
VAR BaseTable = SUMMARIZE( 'GBP', 'Dates2'[Month Year], 'GBP'[Source], 'GBP'[Rate] )
VAR ConvertedValueGBP = SUMX(
	BaseTable,
	VAR MaxDate = IF(MAX('Dates2'[Month Year Formula]) >= "202501", DATE(2025,1,31), CALCULATE( MAX( 'Dates2'[Date] ) ))
    VAR MaxDateStart = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'GBP'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR] ),
		Combined[End Date] > MaxDate, 
        Combined[Start Date] <= MaxDateStart,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'GBP'[Rate]
	RETURN Result
)
VAR BaseTable2 = SUMMARIZE( 'EUR', 'Dates2'[Month Year], 'EUR'[Source], 'EUR'[Rate] )
VAR ConvertedValueEUR = SUMX(
	BaseTable2,
	VAR MaxDate = IF(MAX('Dates2'[Month Year Formula]) >= "202501", DATE(2025,1,31), CALCULATE( MAX( 'Dates2'[Date] ) ))
    VAR MaxDateStart = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'EUR'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR]),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDateStart,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'EUR'[Rate]
	RETURN Result
)
VAR UnconvertedValue = SUMX( VALUES( Dates2[Month Year] ),
	VAR MaxDate = IF(MAX('Dates2'[Month Year Formula]) >= "202501", DATE(2025,1,31), CALCULATE( MAX( 'Dates2'[Date] ) ))
    VAR MaxDateStart = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Result = CALCULATE(
		SUM( Combined[MRR $] ),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDateStart,
        Combined[PON or Not] IN { "PON", "Excluded" },
		ISBLANK( Combined[Currency] ) 
	)
	RETURN Result
)
VAR Result = ConvertedValueGBP + UnconvertedValue + ConvertedValueEUR
RETURN Result

Lodan_1-1739208904479.png

 

Message 5 of 5
291 Views
0
Lodan
Helper II
Helper II

‎02-10-2025 09:30 AM

Actually not quite square one.  Some random months seem ok.

 

Lodan_0-1739208538100.png

 

Message 4 of 5
296 Views
0
bhanu_gautam
Super User
Super User

‎02-10-2025 07:37 AM

@Lodan Try using

 

MRR Revenue =
VAR BaseTable = SUMMARIZE( 'GBP', 'Dates2'[Month Year], 'GBP'[Source], 'GBP'[Rate] )
VAR ConvertedValueGBP = SUMX(
BaseTable,
VAR MaxDate = IF(MAX('Dates2'[Month Year]) = "Jan 2025", DATE(2025,1,31), CALCULATE( MAX( 'Dates2'[Date] ) ))
VAR Source = 'GBP'[Source]
VAR Result = CALCULATE(
SUM( Combined[MRR] ),
Combined[End Date] > MaxDate,
Combined[Start Date] <= MaxDate,
Combined[PON or Not] IN { "PON", "Excluded" },
Combined[Currency] = Source
) * 'GBP'[Rate]
RETURN Result
)
VAR BaseTable2 = SUMMARIZE( 'EUR', 'Dates2'[Month Year], 'EUR'[Source], 'EUR'[Rate] )
VAR ConvertedValueEUR = SUMX(
BaseTable2,
VAR MaxDate = IF(MAX('Dates2'[Month Year]) = "Jan 2025", DATE(2025,1,31), CALCULATE( MAX( 'Dates2'[Date] ) ))
VAR Source = 'EUR'[Source]
VAR Result = CALCULATE(
SUM( Combined[MRR]),
Combined[End Date] > MaxDate,
Combined[Start Date] <= MaxDate,
Combined[PON or Not] IN { "PON", "Excluded" },
Combined[Currency] = Source
) * 'EUR'[Rate]
RETURN Result
)
VAR UnconvertedValue = SUMX( VALUES( Dates2[Month Year] ),
VAR MaxDate = IF(MAX('Dates2'[Month Year]) = "Jan 2025", DATE(2025,1,31), CALCULATE( MAX( 'Dates2'[Date] ) ))
VAR Result = CALCULATE(
SUM( Combined[MRR $] ),
Combined[End Date] > MaxDate,
Combined[Start Date] <= MaxDate,
Combined[PON or Not] IN { "PON", "Excluded" },
ISBLANK( Combined[Currency] )
)
RETURN Result
)
VAR Result = ConvertedValueGBP + UnconvertedValue + ConvertedValueEUR
RETURN Result




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 2 of 5
317 Views
0
Lodan
Helper II
Helper II
In response to bhanu_gautam

‎02-10-2025 09:21 AM

Hey, I thought that was looking promising.  I did change the formula for 'Start Date' so it had its own variable.

 

That fixed for Jan but then the rest of the months of the year followed the previous formula.  So I thought it might work if I change the ="Jan 2025" to >="Jan 2025" but that took me back to square 1.

 

Forecast MRR = 
VAR BaseTable = SUMMARIZE( 'GBP', 'Dates2'[Month Year], 'GBP'[Source], 'GBP'[Rate] )
VAR ConvertedValueGBP = SUMX(
	BaseTable,
	VAR MaxDate = IF(MAX('Dates2'[Month Year]) >= "Jan 2025", DATE(2025,1,31), CALCULATE( MAX( 'Dates2'[Date] ) ))
    VAR MaxDateStart = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'GBP'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR] ),
		Combined[End Date] > MaxDate, 
        Combined[Start Date] <= MaxDateStart,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'GBP'[Rate]
	RETURN Result
)
VAR BaseTable2 = SUMMARIZE( 'EUR', 'Dates2'[Month Year], 'EUR'[Source], 'EUR'[Rate] )
VAR ConvertedValueEUR = SUMX(
	BaseTable2,
	VAR MaxDate = IF(MAX('Dates2'[Month Year]) >= "Jan 2025", DATE(2025,1,31), CALCULATE( MAX( 'Dates2'[Date] ) ))
    VAR MaxDateStart = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'EUR'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR]),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDateStart,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'EUR'[Rate]
	RETURN Result
)
VAR UnconvertedValue = SUMX( VALUES( Dates2[Month Year] ),
	VAR MaxDate = IF(MAX('Dates2'[Month Year]) >= "Jan 2025", DATE(2025,1,31), CALCULATE( MAX( 'Dates2'[Date] ) ))
    VAR MaxDateStart = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Result = CALCULATE(
		SUM( Combined[MRR $] ),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDateStart,
        Combined[PON or Not] IN { "PON", "Excluded" },
		ISBLANK( Combined[Currency] ) 
	)
	RETURN Result
)
VAR Result = ConvertedValueGBP + UnconvertedValue + ConvertedValueEUR
RETURN Result

 

Message 3 of 5
297 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

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

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All