Shoow me last 12 Month starting from the current month

[beck_bakytbek]

Hi Folks

i got have a case with the following example data:

Date	Turonover
10.10.2020	5
03.11.2020	10
15.12.2020	15
02.01.2021	20
06.02.2021	25
13.03.2021	30
17.04.2021	15
22.05.2021	10
26.06.2021	5
31.07.2021	10
04.09.2021	12
09.10.2021	4

My expected output is: starting from current month (09.10.2021 = Okt = 4) i want to see the last 12 Month , i am using this expression,

sum({<Date = {'>=$(=Max(Monthstart(AddMonths(Today(),-12))) ) <$(=Max(Addmonths(MonthEnd(Today()),-1)))'}>}Turnover)

but it does not work, am i missing something?

Does anybody have any idea how to resolve this issue?

Thanks a lot in advance for any replies.

Bek

[Vegar]

Try using double quotes inside your modifier, like this.

sum({<Date = {">=$(=Monthstart(AddMonths(Today(),-12))) <$(=Addmonths(MonthEnd(Today()),-1))"}>}Turnover)

I also removed your max() function as today() will always return a single value and therefore no need for the max().

[abhijitnalekar]

Hi @beck_bakytbek,

Please use change expression as the below 

sum({<Date = {'>=$(=Max(Monthstart(AddMonths(Today(),-12))) ) < $(=Max(MonthEnd(Today())) '}>}Turnover)

[beck_bakytbek]

Hello Vegar, thanks a lot for your reply and your time, i builded your suggestion in expression, but i dont see my current month (Okt-2021), am i missing something?

Thanks a lot

[beck_bakytbek]

Hi @abhijitnalekar  thanks a lot for your suggestion and your time, i builded your suggestion, but it doesn't work, am i missing something?

[Vegar · Accepted solution]

The upper limit in your modifier is <$(=Addmonths(MonthEnd(Today()),-1)). If today is 21th October 2021, then the upper limit will be 30th September 2021. Try to adjust the upper limit to meet your needs.  Maybe this will give you the expected output.

sum({<Date = {">=$(=Monthstart(AddMonths(Today(),-12))) <=$(=MonthEnd(Today()))"}>}Turnover)

[abhijitnalekar]

My Bad. While copy paste i missed one closing bracket. please find updated expression below

sum({<Date = {'>=$(=Max(Monthstart(AddMonths(Today(),-12))) ) < $(=Max(MonthEnd(Today()))) '}>}Turnover)

[beck_bakytbek]

 Hi @Vegar  thanks a lot for your valuable time and your help. it does work.

Have a nica day and stay safe

Bek

[beck_bakytbek]

 Hi @abhijitnalekar   thanks a lot for your valuable time and your help.

Have a nica day and stay safe

Bek