I want to create a baseline revenue value for my FY20 (Apr 2019 to Mar 2020) which happens to be pre covid). This initially worked great with SAMEPERIODLASTYEAR -2 but now with a new fiscal approaching SAMEPERIODLASTYEAR -3 will only work if F23 is selected in the report slicer / filter. So I thought, simple I will just create a measure that has F20 as baseline.
This formula works fine but I have had to put in ALLEXCEPT to deal with the fact that I still want my report page Month slicer to work. From what I read online the ALLEXCEPT can have dangerous context implications if used incorrectly.
All I want to know is how to create a baseline F20 revenue measure that still respects all slicers on page (eg Month, State, Property etc)
Can you create a pbix for the same and let us know what actually you want to achieve because i am not clear by your line where you said it should respect other filters .
Hi @phollihn, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.
A perfect initial question includes all of the following:
A clear explanation of the problem you are experiencing
A mockup of the results you want to achieve
Your current work-in-progress PBIX file
Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)
Thanks for your time. My PBIX file has a lot of sensitive data in it. Perhaps I can make one but first let me try by saying the only thing I meant by respect filters like month was simply if I am on a report page with slicers like FY and Month and I select F23 or F22 and April I’d like the formula to show my the selected compared against F20 for April. I have all the measures and data structure with dates table working and related. I currently used a formula to compare against F20 using DATEADD -2 but not that I am in F23 which started April the formula doesnt work so I want to show a variance against F20 no matter what current FY22 or F23 I select.
Here are my current formulas:
Revenue = SUM( ‘Revenue’[Values])
Revenue PY = Calculate( [Revenue], SAMEPERIODLASTYEAR(‘dates’[Date]))
Revenue to F20 = Calculate([Revenue], DATEADD(‘dates’[Date]), -2, YEAR)
Revenue Var% to F20 = DIVIDE(([Revenue]-[Revenue to F20]),[Revenue to F20])
As you can see the Revenue Var% to F20 measure doesnt work anymore when I use my report page slicers to select FY23 and April for example.
So I came up with a measure:
Revenue to F20 New = CALCULATE([Revenue],ALLEXCEPT(Dates,Dates[Month],Dates[FY_Month]),(Dates[Fiscal_Year])=2020)
Although it works when I compare against this new measure with the following I am told the use of ALLEXCEPT is a potentially problematic function that can be taken out of context if used incorrectly on another report. So I am looking for the correct way to address having a measure that is just F20 to compare CY against.
Revenue Var% to F20 = DIVIDE(([Revenue]-[Revenue to F20 New]),[Revenue to F20 New])
Sorry if this is too onerous or confusing I will take the time to create a dummy PBIX file.
Thanks Keith that link was helpful. I have included my Practice Dataset PBIX file to help with the problem.
The problem:
I want to compare against a specific fiscal year. eg. Total Sales for current FY variance to Total Sales for specific Fiscal Year lets say FY19
I have created a measure that works (see Practice Dataset): Total Sales F19 = calculate([Total Sales],ALLEXCEPT(Dates,Dates[Month Name]),(Dates[Fiscal Year])=“FY19”)
It works but I am concerned that this is not the best practice to use ‘ALLEXCEPT’ because it may cause issues if reused by in report where the context might change.
Perhaps ‘ALLEXCEPT’ is the way to go but I’ve read that it can cause problems.
Hi @phollihn, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @phollihn, we’ve noticed that no response has been received from you since May 5th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @phollihn, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.