Hi everyone, could someone please help me with this scenario?
I need to sum the values Qty considering the period, so my dataset is like this:
| Product |
Date |
Qty |
KEY |
Sum |
Aggr |
| 1 |
01/12/2021 |
93 |
1|10|20|Text |
0 |
0 |
| 1 |
01/11/2021 |
93 |
1|10|20|Text |
0 |
0 |
| 1 |
01/08/2021 |
279 |
1|10|20|Text |
465 |
279 |
| 2 |
01/09/2020 |
93 |
2|10|20|Text2 |
0 |
93 |
sum measure:
sum(aggr(Sum({$<[Date]={"$(='>=' & AddMonths(MonthStart(max([Date])),-11)& ' <= ' & Date(Max([Date]), 'DD/MM/YYYY'))"}>} Qty),[Key]))
aggr measure:
=sum(aggr(FirstSortedValue(Qty ,-aggr(max(Date),[Key])),[Key]))
The expected result is:
| Product |
KEY |
Sum |
| 1 |
1|10|20|Text |
465 |
| 2 |
2|10|20|Text2 |
93 |
thanks in advance
