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
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
Solved! Go to 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
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
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!
Proud to be a Super User! | |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
102 | |
86 |