Power BI / DAX - Year on Year Comparison when partial year has elapsed

Question

I'm very new to Power BI and I want to create a tile in power bi where it returns the Year on Year different in total users. There is a slicer to select your desired year (i.e.) if 2023 is selected it will calculate the YOY % between 2022 and 2023

However, when 2024 is selected in the slicer the it seems to be comparing the time that has elapsed so far this year with the entirety of 2023 rather than the same time period in 2023. When I return the Users Current Year, Users Previous Year, Year and Month on a table I can see the calculation is correct for each month however the tile is returning the full year comparison rather than the same time period. I am calculating the variables as below:

Users CY = 
VAR MaxDate = MAX('Calendar Table'[Date]) 
RETURN 
IF(
    SELECTEDVALUE('Calendar Table'[Calendar Year]) = YEAR(MaxDate),
    CALCULATE(
        COUNT(Table[user_id]),
        FILTER(
            'Calendar Table',
            'Calendar Table'[Date] <= MaxDate &&
            'Calendar Table'[Date] >= DATE(YEAR(MaxDate), 1, 1)
        )
    ),
    CALCULATE(
        COUNT(Table[user_id]),
        FILTER(
            'Calendar Table',
            'Calendar Table'[Calendar Year] = YEAR(MaxDate)
        )
    )
)

Users PY =
CALCULATE(
    COUNT(Table[user_id]),
    DATEADD('Calendar Table'[Date], -1, YEAR)
)

Users YoY% =
VAR Users_CY = [Users CY]
VAR Users_PY = [Users PY]
RETURN
IF(
    ISBLANK(Users_PY) || Users_CY = 0,
    BLANK(),
    (Users_CY - Users_PY) / Users_PY
)

I have also tried using SAMEPERIODLASTYEAR but still getting the same result

I want this to dynamically update (i.e.) the data will be refreshed every month so when March 2024 data is added to the table it'll automatically update the calculation

Answer 1

The reason you're getting the full year comparison is because when you select 2024 in the data slicer, the date table is filtered down to all of 2024(365 days). Thus, when SAMEPERIODLASTYEAR evaluates, it will compare to all of 2023. Perhaps, instead of using the date slicer, you put the year or month on our x-axis like this.

You could also possibly add field parameters to let the user pick if they want months/years/or quarters to slice the data by.

Lastly, to remove the full year of 2024 on your table/chart, you could add a visual filter of when the month <= current month. This can be challenging if you don't have a date table or offset columns. I wrote about date calendars and offsets here.