Last 4 Quarters

[JPanzer]

Hi all,

I got revenue data for the last years with quarters loaded in the script.

I now want to create a table which shows me dynamically the sum of revenues for the chosen quarter and the three quarters prior.

The columns for the current quarter and the last quarter are working correctly.
I just got problems with quarter_now -2 and -3.

My formula for -3 is:

if(match(max(QuartalNr), 1,2,3)
, sum({<Jahr = {$(=Max(Jahr)-1)}, QuartalNr = {$(=max(QuartalNr)+1)}>}[Lagerausgang.Bonpositionen])
, sum({<Jahr = {$(=Max(Jahr))}, QuartalNr = {$(=max(QuartalNr)-3)}>}[Lagerausgang.Bonpositionen]))

(Jahr is Year, QuartalNr is the number of the quarter (1-4) und Lagerausgang.Bonpositionen is the revenue)

If I filter for 2022 Q3, it correctly shows me data for 2021 Q4. But if I filter for 2022 Q2 or Q1, it shows me no data, but there is data for these quarters...

Similar problem for the - 2 Quarters column:

if(Match(max(QuartalNr), 1,2)
, sum({<Jahr = {$(=Max(Jahr)-1)}, QuartalNr = {$(=max(QuartalNr)+2)}>}[Lagerausgang.Bonpositionen])
, sum({<Jahr = {$(=Max(Jahr))}, QuartalNr = {$(=max(QuartalNr)-2)}>}[Lagerausgang.Bonpositionen]))  

If I filter for 2022 Q2, it correctly shows me data for 2021 Q4, but for the filter for 2022 Q1 I get no data again.

I am not sure where the error is, since it works basically for a change to the last year, but just for Q4...

Thanks in advance,
Julian

[Oliver_F · Accepted solution]

Hi,

I think the easiest way to do this is to change your set analysis like this

sum({">=$(monthstart(addmonths(today(),-3)<=$(monthend(addmonths(today(),-1)))"}[Lagerausgang.Bonposition])

syntax might be a little off as I typed it here and did not check it in the editor, but with that input you should be able to change the formula to your needs.

you can even use variables like vD_Month_R3_Start and vD_Month_R3_End to make your formula more readable.