GetFieldSelection Previous Year Value

SindhuraK — Mon, 14 Nov 2022 22:48:40 GMT

I am using GetFieldselction function and getting the value of Filtered year using below formula.

Sum({<[MonthYear]={"$(=GetFieldSelections(MonthYear))"}>}[Total Income])/1000000 --This gives me Filtered year value correctly

Now, based on filter selction, for example if I choose filter as Oct-22, I would like to get Oct-21 values in other field.

I tried the below but it doesn't work.

Sum({<[MonthYear]={"=$=AddYears(=GetFieldSelections(MonthYear)),-1)"}>}[Total Income ])/1000000.

Can anyone please tell me how to get previous year value based on filter selection using "GetFieldselction"?

Re: GetFieldSelection Previous Year Value

Michiel_QV_Fan — Tue, 22 Nov 2022 15:22:13 GMT

Consider adding a serial date field to your master calendar: Autonumber(MonthYear) as _YM_Serial. Make sure your calendar is ascending sorted.

Then calculate like this:

Sum({<_YM_Serial = {$(=Max(_YM_Serial))}>}[Total Income]).

and

Sum({<_YM_Serial = {"=$(=Max(_YM_Serial)-1)"}>}[Total Income]) for the previous year.

Selecting okt 2022 will yield that result. Selecting oct 2021 will yield that months result since your calendar would like something like this:

MonthYear , _YM_Serial

oct. 2021 , 1

oct. 2022 , 2