Hello,
I'm trying to find out the effective ratio for the years 2022 and 2021 and try to compare them . I usually used this calculation for the current year:
IF YEAR([Booked Date]) = {MAX(YEAR([Booked Date])) }
THEN ([Financed Amount])
END
and for the previous year:
IF YEAR([Booked Date]) = {MAX(YEAR([Booked Date]))} -1
THEN ([Financed Amount])
END
Now I ran into the problem that the effective ratio (Sum(...)/Sum(...)) is an aggregated measure and can't be used with the previous calculation.
I tried to aggregate the IF and THEN:
IF MAX(YEAR([Booked Date])) = WINDOW_MAX(MAX(YEAR([Booked Date])))
THEN ([Effective Ratio])
END
Now I ran into the problem that it won't use the correct year and takes both year into the calculation.
Can someone please explain this and provide a solution?
Thanks much in advance!!!
Marcel
@Marcel Fuhrer Hi Marcel
I have updated the workbook you have supplied with a calculation that I hope will bring resolution to your concern.
I have created 2 new calculated fields called [Ratio] and [Max Year].
In the calculation [Max Year], it basically calculates the latest year in the [Booked Date] range of dates and is used to populate the entire column using the FIXED LOD notation. This will ensure that the latest year is used as a reference by all other rows in the data.
The other calculated field [Ratio] then checks [Booked Date] grouped into each years against the [Max Year] and if it is either equal to this latest year or one year prior, it proceeds to perform the division of sum([FA of Effective])/sum([Financed Amount]).
As a result, we have a crosstab that looks like the below.
Due to the fact that we are only interested in seeing the results at each year level, we could do this by changing the [Booked Date] pill on the Rows shelf to only represent the discreet year. By doing so the same crosstab will look like the below image.
Pleaser refer to the updated workbook and let me know if this helps.
