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