Power BI Slicer to affect only one column (Current Year YTD column) and not other columns

[kskumar]

Hi Folks,

I have to produce a table visual in Power BI, to report the sales performance for current year (2024) with previous year sales data (2022 to 2023) for 5 to 7 products. I have also a slicer that would display the 12 months where the user can select the month to affect ONLY the YTD sales column.  However, if I choose the month in the slicer, it impacts the output for previous year sales data as well.

I have two tables

1. DATA table with year 2022 to year 2024 sales details

2. Date table, created using Calendar function with 01Jan2024 to 31Dec2024.

These two tables are linked with date fields in Date table and DATA table.

SOURCE:

DATATable:

Product     Year       Sales

Prod1        2022      $30,000

Prod2        2022       $15,000

...

...

DateTable:

= CALENDAR(DATE(2024,1,1), DATE(2024,12,1))

Measures Created 

Year2022 = CALCULATE(SUM(DATATable[Sales]), FILTER(DataTable, DataTable[Year] = "2022")

Year2023 = CALCULATE(SUM(DATATable[Sales]), FILTER(DataTable, DataTable[Year] = "2023")

YTD2024 = TOTALYTD(SUM(DATATable[Sales]), DateTable[Date])

If I choose the Feb month from the Slicer, Columns Year2022 & Year2023 (in output Table) have become zero, as opposed to have expected fixed value for Year2022 & Year2023 regardless of whatever we choose in Slicer. 

I tried using ALL, REMOVEFILTERS function in the measures "Year2022" & "Year2023", but still the same output.

Please provide your valuable inputs. Kindly let me know if you need any info from my end.

Output (Expected)

Product     Year2022   Year2023    YTD2024

Prod1        $100,000     $120,000   $40,000

Prod2        $110,000     $95,000     $25,000

Output (Actual Results)

Product     Year2022   Year2023    YTD2024

Prod1        $0             $0               $40,000

Prod2        $0             $0               $25,000

Regards

Kumar

[Ashish_Mathur]

Hi,

Why is there no month column in the Data Table?

[Bmejia]

Can you just create the measures with out using the "filter"

CALCULATE(SUM(DATATable[Sales]),  DataTable[Year] = "2022") 
or just create a matrix table with years as header and sales as values, your current year would be YTD.

if you looking for the value to remain static even when you select a year or month
Year2023 = CALCULATE(SUM(DataTable[Sales]), 'Date'[Year] =2023, ALL('Date'[Year],'Date'[Month]))