RE: Powerbi Newbie trying to understand why a measure is not being picked up in switch

[MrSunsoa]

Hello All,

I have a Previous Year Sales 

I am using the Superstore dataset which has max date of 2022 so in wanted to calculate previous year sales

PreviousYearSales =

CALCULATE(

    [Total Sales],

    ALLEXCEPT(Orders, Orders[Order Date]),

    YEAR(Orders[Order Date]) = 2021)

(After lots of googling it pointed me to this after being prompted by a circular dependancies

The measure does not get recognised

Compare Switch =

SWITCH(

    TRUE(),

    [User Selection] = "PY Sales", PreviousYearSales

    [User Selection] = "Sales Target", TotalSalesTarget

)

Any help is appreciated

[rajendraongole1]

Hi @MrSunsoa - Instead of using ALLEXCEPT, which can create unintended side effects and circular dependencies, you can use the SAMEPERIODLASTYEAR function to calculate the previous year's sales

can you use below measure

PreviousYearSales =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(Orders[Order Date])
)

Now, you can proceed with your CompareSwitch measure using the SWITCH function

CompareSwitch =
SWITCH(
TRUE(),
[User Selection] = "PY Sales", [PreviousYearSales],
[User Selection] = "Sales Target", [TotalSalesTarget],
[Total Sales] // Default case if no match is found
)

for users selection , you have already a seperate calculated table 

UserSelectionTable =
DATATABLE(
"Selection", STRING,
{
{"PY Sales"},
{"Sales Target"}
}
)

capture for user selection as another measure by calling the above table

User Selection =
SELECTEDVALUE(UserSelectionTable[Selection])

hope this works

[MrSunsoa · Accepted solution]

Thank you, the problem was the Current Year and Last Year were columns not measures it was the icon that threw me. The reason i used Year = 2022 for Current year and Year =2021 for Previous year was the asummption that SAMEPERIODLASTYEAR was relative today. All has been resolved and i have created better checks

[rajendraongole1]

Hi @MrSunsoa -I'm glad to hear that everything is now working smoothly! Your approach of using the specific year comparison for Current Year and Previous Year makes sense in scenarios where you want to be very explicit about the year range, rather than relying on SAMEPERIODLASTYEAR, which is relative to the current date.

If you need further assistance with DAX formulas or Power BI reports, feel free to ask!

Thank you!