Forum Discussion
Simple formula question
Hi everyone!
I want to get cumulative numbers for the previous month and the month by formula.
By the way, the month is selected as the filter and behaves differently.
When you have the following data:
| Attribute | Value |
| P01 | 149 |
| P02 | 192 |
| P03 | 140 |
| P04 | 137 |
| P05 | 129 |
| P06 | 207 |
| P07 | 145 |
| P08 | 150 |
| P09 | 273 |
| P10 | 196 |
| P11 | 45 |
| P12 | 0 |
I want to get the maximum period of data with values in the formula, and then I want to get the cumulative value excluding the period.
The maximum period of the data was brought to the formula and unlike hardcoded, it doesn't work at all.
Of course, you can add pre priod fields in the data, but the amount of original data is huge.
I want to solve it by formula.
max_period = MAXX(FILTER(Sheet2,Sheet2[Value] > 0),Sheet2[Attribute])
pre_preiod = "P"&FORMAT(INT(RIGHT([max_period],2))-1,"00")
pre_accum = SUMX(FILTER(Sheet2,Sheet2[Attribute] <> [max_period]),Sheet2[Value])
pre_accum2 = SUMX(FILTER(Sheet2,Sheet2[Attribute] <> "P11"),Sheet2[Value])
2 Replies
- Can you be more specific about just what your problems is ? do the big blue arrows represent calculations that don't work ?
