Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MrSunsoa
New Member

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

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
1 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

View solution in original post

3 REPLIES 3
rajendraongole1
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.