Sum using slicer

[Anonymous]

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.

[Fowmy]

@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

[Anonymous]

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.

[Fowmy]

@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?

[Anonymous]

Hi @Fowmy ,

Thanks for the response.

With 3 years of data(2022,2023,2024), 

When  we select 2022 in the slicer : 2022 will be the current year and 2023 will be compared year.

When  we select 2023 in the slicer: 2023 will be current year and 2022 will be compared year(As we don't have full year of 2024 data so we will be comparing it with 2022 only.).

When we select 2024 in slicer: 2024 will be current year and 2023 will be compared year.

Thanks in advance.

[Fowmy · Accepted solution]

@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

[Rare_analyst]

I am getting belwo error while using above created measure in the calculation:

what should i do?