Hi,
I hope someone can help me with this issue I'm having trying to filter the first value in a dataset. I haved a sample set of data as per the table below. Im using some standard functions with set values but I'm trying to filter one and it's not working.
My first expression is sum(BudgetValue) with Month as the dimension. When a user selects a Cycle Month they get the Month Values for that cycle and this works correctly.
My second expression gets the month values for the previous cycle with the following expression
sum({<CycleID={$(=CycleID-1)} BudgetValue) and this also works.
The expression thats not working is when I try to combine the 2 expressions above but only add the first value from the previous cycle. For example, if a user selects Cycle Month Mar (Cycle ID 3), I want my table to display
Feb Mar Apr May Jun
240 230 270 240 220
Using the following expression sum(BudgetValue) + sum({<CycleID={$(=CycleID-1)} I am getting the following
Feb Mar Apr May Jun
240 450 540 490 440
i.e. it is adding all of the budget values for CycleID 3 and 2. I only want to add the first value for CycleID 2 to all of the values for CycleID 3. I have trying to use the First function and FirstSortedValue function but this hasn't worked.
Can anyone advise on what function / set function I could use to get my desired results.
Thanks.
| Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | Header 6 | Header 7 | Header 8 |
|---|
| Cycle ID | 1 | 2 | 3 | 4 | 5 | 6 | |
| Cycle Month | Jan | Feb | Mar | Apr | May | Jun | |
| Jan | 200 | 250 | 240 | 280 | 230 | 210 | |
| Feb | | 240 | 220 | 270 | 250 | 220 | |
| Mar | | | 230 | 270 | 240 | 220 | |
| Apr | | | | 280 | 250 | 240 | |
| May | | | | | 240 | 230 | |
| Jun | | | | | | 220 | |
