Forum Discussion
Numbers not matching
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
- Lodan1 year agoHelper II
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