YoY Metric is not working

[Anonymous]

Please help"

I have a data set which looks like the following:

Month-Start	Region	Car Brand	Number of Cars
2022-02-01	A	BMW	0
2022-02-01	B	Volvo	50
2024-09-01	C	Toyota	100

Data ranges from 2022-01 to 2024-09, per month, per brand, number from 0 to Xk

Then I have a measure WOJ_Cars which is simply a Sum of (number of cars)

I built a metric: WOJ_Num_Cars_LY which follows logic:

- If I select from a slicer (dim_date, linked)

- year 2024 : then show me data from 2023 till end of September

- year 2023: then show me entire 2023

- year 2022: then show me "no data"

WOJ_NumCars_LY = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(dim_date[Year]) = 2024, 
        CALCULATE(
            [WOJ_Cars],
            dim_date[Year] = 2023 ,
            dim_date[Month-Start] <= DATE(2023, 09, 30)  -- Up to SEP 30, 2023
        ),
    SELECTEDVALUE(dim_date[Year]) = 2023, 
        CALCULATE(
            [WOJ_Cars],
            dim_date[Year] = 2022  -- Entire year 2022
        ),
    SELECTEDVALUE(dim_date[Year]) = 2022, 
        "No Data",  -- Show "no data" (can display as blank or custom text in visuals)
    "Choose a Year for (YoY)"  -- Default case if no year matches
)

The foruma is as above. And this formula gives me wrong results and cannot figure out why. Selecting 2022 is correct but the rest is not:

[ajohnso2]

Change your dax as below, essentially you need to clear the filter on Year (Since your selected value from slicer forces a filter)

WOJ_NumCars_LY = 
VAR _SY = SELECTEDVALUE(dim_date[Year])
SWITCH(
    TRUE(),
    _SY = 2024, 
        CALCULATE(
            [WOJ_Cars],
ALL(dim_date[Year]),       
dim_date[Year] = 2023 ,
            dim_date[Month-Start] <= DATE(2023, 09, 30)  -- Up to SEP 30, 2023
        ),
    _SY = 2023, 
        CALCULATE(
            [WOJ_Cars],
ALL(dim_date[Year]),
            dim_date[Year] = 2022  -- Entire year 2022
        ),
    _SY = 2022, 
        "No Data",  -- Show "no data" (can display as blank or custom text in visuals)
    "Choose a Year for (YoY)"  -- Default case if no year matches
)

[Anonymous]

Hello @ajohnso2 thanks for the reply, this does not give me different results.

I think in your suggestion there is a RETURN statement missing, after the second line so I added it, now it looks like that:

WOJ_NumCars_LY = 
VAR _SY = SELECTEDVALUE(dim_date[Year])
RETURN
SWITCH(
    TRUE(),
    _SY = 2024, 
        CALCULATE(
            [WOJ_Cars],
            ALL(dim_date[Year]),       
            dim_date[Year] = 2023,
            dim_date[Month-Start] <= DATE(2023, 09, 30)  -- Up to SEP 30, 2023
        ),
    _SY = 2023, 
        CALCULATE(
            [WOJ_Cars],
            ALL(dim_date[Year]),
            dim_date[Year] = 2022  -- Entire year 2022
        ),
    _SY = 2022, 
        "No Data",  -- Show "no data" (can display as blank or custom text in visuals)
    "Choose a Year for (YoY)"  -- Default case if no year matches
)

the result looks the same:

[ajohnso2]

Ah yes good spot.

Are you able to share a sample pbix?

[Anonymous]

Hello @ajohnso2  , sure: https://we.tl/t-uzVXDjCFWr (it is WeTransfer)

[ajohnso2]

I've had to change it around a little including a new date table

SAMPLE_MOTO.pbix 

[Anonymous]

Thanks for that @ajohnso2 , I am wondering what the problem was there.

I replicated your pattern for date table in my file, and notice that weird stuff is happening based on which date I am chosing.

If i pick a format year-month I am getting the right number:

but when I choose date hierarchy form month-start I am getting this rubbish:

it is from the same table, I dont know why this is happening. Would you know?