Hello Guys,
Help me in below set expression , not able to achieve this from long time.
Requirement is to get the Rolling of sales
Time period to considered is (Previous 9 months + Current Month + Next 2 months)
Below is the sample data, mentioned rolling period below for understanding.
Same KPI is Used in Tables charts, KPI box.
Expression i tried is as below.
Sum({<Date = {">=$(=MonthStart(AddMonths(Date(Max(Date)),-9)))
<=$(=Monthend(AddMonths(Date(Max(Date)),2)))"}>}Sales)
Issue:Max date should calculate row level in table, but above expression max date is calculated on whole App.
I can't use Range sum because some months data is missing and also the same kpi is mentioned in KPI Box and many charts with lowest granularity.
Thanks in Advance 🙂
| Product | Date | Year | Month | Sales | Sales_Pred | Rolling Period to be considered(Prev 9 + CurMon + Next 2) |
| P01 | 4/1/2020 | 2020 | 4 | 100 | 330 | 04/2020 to 06/2020 |
| P01 | 5/2/2020 | 2020 | 5 | 110 | 460 | 04/2020 to 07/2020 |
| P01 | 6/2/2020 | 2020 | 6 | 120 | 600 | 04/2020 to 08/2020 |
| P01 | 7/3/2020 | 2020 | 7 | 130 | 750 | 04/2020 to 09/2020 |
| P01 | 8/3/2020 | 2020 | 8 | 140 | 910 | 04/2020 to 10/2020 |
| P01 | 9/3/2020 | 2020 | 9 | 150 | 1080 | 04/2020 to 11/2020 |
| P01 | 10/4/2020 | 2020 | 10 | 160 | 1280 | 04/2020 to 12/2020 |
| P01 | 11/4/2020 | 2020 | 11 | 170 | 1460 | 04/2020 to 01/2021 |
| P01 | 12/5/2020 | 2020 | 12 | 200 | 1620 | 04/2020 to 02/2021 |
| P01 | 1/5/2021 | 2021 | 1 | 180 | 1760 | 04/2020 to 03/2021 |
| P01 | 2/5/2021 | 2021 | 2 | 160 | 1780 | 05/2020 to 04/2021 |
| P01 | 3/8/2021 | 2021 | 3 | 140 | 1770 | 06/2020 to 05/2021 |
| P01 | 4/8/2021 | 2021 | 4 | 120 | 1650 | 07/2020 to 06/2021 |
| P01 | 5/9/2021 | 2021 | 5 | 100 | 1520 | 08/2020 to 07/2021 |
| P02 | 4/1/2020 | 2020 | 4 | 80 | 180 | |
| P02 | 5/2/2020 | 2020 | 5 | 60 | 200 | |
| P02 | 6/2/2020 | 2020 | 6 | 40 | 330 | |
| P02 | 7/3/2020 | 2020 | 7 | 20 | 490 | |
| P02 | 8/3/2020 | 2020 | 8 | 130 | 680 | |
| P02 | 9/3/2020 | 2020 | 9 | 160 | 900 | |
| P02 | 10/4/2020 | 2020 | 10 | 190 | 1150 | |
| P02 | 11/4/2020 | 2020 | 11 | 220 | 1430 | |
| P02 | 12/5/2020 | 2020 | 12 | 250 | 1740 | |
| P02 | 1/5/2021 | 2021 | 1 | 280 | 2080 | |
| P02 | 2/5/2021 | 2021 | 2 | 310 | 2370 | |
| P02 | 3/8/2021 | 2021 | 3 | 340 | 2710 | |
| P02 | 4/8/2021 | 2021 | 4 | 370 | 2670 | |
| P02 | 5/9/2021 | 2021 | 5 | 400 | 2650 | |
