Forum Discussion
Sum using slicer
Hello Power Bi community,
i have data in below format:
| Year | Month | Sales | Profit |
| 2023 | 1 | 24 | 11 |
| 2023 | 2 | 56 | 33 |
| 2023 | 2 | 76 | 22 |
| 2023 | 1 | 78 | 66 |
| 2023 | 2 | 98 | 33 |
| 2022 | 2 | 76 | 44 |
| 2022 | 1 | 90 | 55 |
| 2022 | 3 | 78 | 78 |
| 2022 | 4 | 67 | 65 |
| 2022 | 5 | 98 | 34 |
| 2022 | 5 | 80 | 12 |
Output:
When i select 2022 in slicer i want output like: 2022 Sales:489
2023 Sales: 332
Similary when i select 2023 in slicer i want output like: 2022 Sales: 166
2023 Sales : 332
reason for above output because 2023 have data till Feb'23 only, so i want to show sum of sales till Feb'22 for 2022 .
Right now when i select the 2023 slicer it shows sum of sales for 2023 and whole year sales sum for 2022 instead of sum of sales for the months data we have in 2023.
Anonymous
I modified the year to accommodate 2024:Sales Compared Year = VAR __CurrentYear = SELECTEDVALUE( Table22[Year]) VAR __Year = SWITCH( __CurrentYear, 2022 , 2023, 2023, 2022, 2024 , 2023 ) VAR __Months = VALUES( Table22[Month] ) VAR __Result = CALCULATE( [Selected Year Sales], Table22[Year] = __Year, __Months ) RETURN
6 Replies
Anonymous
Create two measure as follows:Selected Year Sales = SUM(Table22[Sales ])Sales Compared Year = VAR __CurrentYear = SELECTEDVALUE( Table22[Year]) VAR __Year = IF( __CurrentYear = 2022 , 2023, 2022) VAR __Months = VALUES( Table22[Month] ) VAR __Result = CALCULATE( [Selected Year Sales], Table22[Year] = __Year, __Months ) RETURN __Result
Expected Results:When i select 2022 in slicer i want output like: 2022 Sales:489, 2023 Sales: 332
Similary when i select 2023 in slicer i want output like: 2022 Sales: 166, 2023 Sales : 332
Hi Fowmy ,
Thanks for the solution it is working fine for 2 years of data.
what would be the required to make this sol work for 3 years of data like 2022,2023 and 2024.Anonymous
In that case, how you would you decide which is the current year and which is the compared year. Say, in the lislicer, you pick 2023 and which year will be considered for comparison, 2022 or 2024? what is the logic?