I must calculate the top 10 brands for this year and then get the same figures for last year... but I found it a little bit tricky.
I have the following set for the top 10 brands over year to date measure.
My date is a parameter (Month-Year) and everything is ok so far.
filter(TopCount(
([10 - Brand].[Brand].Allmembers) * {STRTOMEMBER(@DATE)}
,10,
[Measures].[Act YTD]
), [Measures].[Act YTD] <> null)
But now I have to use this set and get the previous year.
The expected results is get the top 10 this year and after, last year figures in the same query:
Year | Quarter | Month | Brand | Act YTD
2019 | 20193 | Aug 2019 | BR01 | 150
2019 | 20193 | Aug 2019 | BR02 | 250
2019 | 20193 | Aug 2019 | BR03 | 350
2019 | 20193 | Aug 2019 | BR04 | 450
2019 | 20193 | Aug 2019 | BR05 | 550
2019 | 20193 | Aug 2019 | BR06 | 650
2019 | 20193 | Aug 2019 | BR07 | 750
2019 | 20193 | Aug 2019 | BR08 | 850
2019 | 20193 | Aug 2019 | BR09 | 950
2019 | 20193 | Aug 2019 | BR10 | 1150
2018 | 20183 | Aug 2018 | BR01 | 100
2018 | 20183 | Aug 2018 | BR02 | 200
2018 | 20183 | Aug 2018 | BR03 | 300
2018 | 20183 | Aug 2018 | BR04 | 400
2018 | 20183 | Aug 2018 | BR05 | 500
2018 | 20183 | Aug 2018 | BR06 | 600
2018 | 20183 | Aug 2018 | BR07 | 700
2018 | 20183 | Aug 2018 | BR08 | 800
2018 | 20183 | Aug 2018 | BR09 | 900
2018 | 20183 | Aug 2018 | BR10 | 1000
I need guidance :)
I found the solution.
Instead of displaying the results in rows, now I am displaying them as columns.
And I had to redesign the report due to row groupings and column groupings.
This way I can create the top 10 set based on this year's date and get the Y-1 measures.
Year | Quarter | Month | Brand | Act YTD | Act YTD -1 |
2019 | 20193 | Aug 2019 | BR01 | 150 | 100 |
2019 | 20193 | Aug 2019 | BR02 | 250 | 200 |
2019 | 20193 | Aug 2019 | BR03 | 350 | 300 |
2019 | 20193 | Aug 2019 | BR04 | 450 | 400 |
2019 | 20193 | Aug 2019 | BR05 | 550 | 500 |
2019 | 20193 | Aug 2019 | BR06 | 650 | 600 |
2019 | 20193 | Aug 2019 | BR07 | 750 | 700 |
2019 | 20193 | Aug 2019 | BR08 | 850 | 800 |
2019 | 20193 | Aug 2019 | BR09 | 950 | 900 |
2019 | 20193 | Aug 2019 | BR10 | 1150 | 1000 |