Measure calculating up to a month in YoY

[Anonymous]

I have a measure which is meant to show the logic:

- measure calculating sum(car) last year from the one selected for a slicer

- If I  select year 2024 from a slicer, then show me sum(car)up to end of October 2023,

- if i select year 2023, then show me sum(car) from entire 2022

- if i select year 2022, show me 'no data'

I tried the approach for the first and second bullet and already getting an error I do not understand:

NumEUCars_YoY =

CALCULATE(

    SUM(EU_REJESTR_OSOB[CARS]),

    IF(

        SELECTEDVALUE(dim_date[Year]) = 2024,

        dim_date[Month] <= 10,  -- Limit to October

        TRUE()  -- No filter for other years

    )

)

-- this gives me an error 'Cannot find name [Month]' - which clearly is there, as int

[Bibiano_Geraldo · Accepted solution]

Hi @Anonymous ,

The error occurs because the column dim_date[Month] is being referenced outside a filter context in the IF condition. DAX does not allow column references directly in logical conditions like this. You need to use the FILTER function to apply conditions on columns.

Please try the bellow DAX:

NumEUCars_YoY =
VAR SelectedYear = SELECTEDVALUE(dim_date[Year])
VAR LastYear = SelectedYear - 1
RETURN
SWITCH(
    TRUE(),
    SelectedYear = 2024,
    CALCULATE(
        SUM(EU_REJESTR_OSOB[CARS]),
        dim_date[Year] = LastYear && dim_date[Month] <= 10
    ),
    SelectedYear = 2023,
    CALCULATE(
        SUM(EU_REJESTR_OSOB[CARS]),
        dim_date[Year] = LastYear
    ),
    SelectedYear = 2022,
    "No Data",
    BLANK()
)

[Anonymous]

Hello @Bibiano_Geraldo , I accepted by mistake as solution and cannot undo it.

I used your solution, and tried looking at month / quarter / year level, still not getting the right result:

ZZZ test =

VAR SelectedYear = SELECTEDVALUE(dim_date[Year])

VAR LastYear = SelectedYear - 1

RETURN

SWITCH(

    TRUE(),

    SelectedYear = 2024,

    CALCULATE(

        SUM(EU_REJESTR_OSOB[CARS]),

        dim_date[Year] = LastYear && dim_date[Month] <= 10

    ),

    SelectedYear = 2023,

    CALCULATE(

        SUM(EU_REJESTR_OSOB[CARS]),

        dim_date[Year] = LastYear

    ),

    SelectedYear = 2022,

    "No Data",

    BLANK()

)

[Bibiano_Geraldo]

Sorry about that @Anonymous , i just downloaded the file in your another post, and now i tested my measure, try it and let me now if it works as expected, note that this updated measure is dynamic for any year:

WOJ_NumCars_LY 2 = 
VAR total = IF(
    ISBLANK([WOJ_Cars]) || [WOJ_Cars] = 0,
    BLANK(),
    CALCULATE(
        [WOJ_Cars],
        SAMEPERIODLASTYEAR(dim_date[Month-Start])
    )
)
RETURN
IF(
    ISBLANK(total) && NOT ISBLANK([WOJ_Cars]),
    "No data",
    total
)

IF this works, please let me know to put in your another post:

[Anonymous]

Hello @Bibiano_Geraldo I took the measure you suggested, and it still does not give the right numbers - take a look at the screenshot. I am using year-month as a grouping category, but when change it to date hierarchy it shows me the same:

However, I had a metric which seems to be working , but not exactly.

MEtric I paste below - it was working with a format year-month but not with date hirearchy for month-start, and I have no idea why. Maybe you would know?

WOJ_NumCars_LY = 
VAR _SY = MAX(dim_date[Year])
VAR _Month = MAX(dim_date[Month])
RETURN
SWITCH(
    TRUE(),
    _SY = 2024, 
        CALCULATE(
            [WOJ_Cars],
            ALL('dim_date'[Year-Month]),       
            dim_date[Year] = 2023,
            dim_date[Month]= _Month,
            dim_date[Month-Start] <= DATE(2023, 09, 30)  -- Up to SEP 30, 2023
        ),
    _SY = 2023, 
        CALCULATE(
            [WOJ_Cars],
            ALL(dim_date[Year-Month]),
            dim_date[Year] = 2022,  -- Entire year 2022
            dim_date[Month] = _Month
        ),
    _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
)

[Bibiano_Geraldo]

Hi @Anonymous , Download the file by this link: SAMPLE_MOTO.pbix .

let me know if its what you asked, if no, please re-share the sample with expected example

[Anonymous]

Thanks @Bibiano_Geraldo  this seems like it is working.!

[Bibiano_Geraldo]

Make your tests, if all ok, let me know, to upload this file in another post your made, just to close this problem. and consider to mark the correct reply as solution, for future user's with the same issue find the solution easily.