Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
This is what I've created so far:
The problem is having a filter for "TY Date" is not client friendly. I want the client to be able to filter a year, say "2014" and then the yoy growth % should show 7.23% (the growth in 2014 over 2013). But when I include the year column, it shows me this:
All the yoy growth % figures disappear. Why is this happening?
Ultimately, I want just two columns in this chart, "Year" and "Yoy growth %" and when I filter for 2014 through the slicer, it should just show me the growth % for 2014 over 2013.
Thanks, would appreciate any help!
Solved! Go to Solution.
The major issue was the TY Date Column which was not really a date (just year)
Anyway change it like this...
1) TY Date
TY Date = DATE ( Sheet1[Transaction Year], Sheet1[Transaction Month], 1)
2) Last Year Sales
Last Year Sales = CALCULATE ( SUM(Sheet1[Sales]), PREVIOUSYEAR (Sheet1[TY Date].[Date]) )
3) YoY Sales Change
YoY Sales Change = DIVIDE ( [This Year Sales]-[Last Year Sales], [Last Year Sales], 0 )
4) Use the TY Date column in the Table Visual and from the Date Hierarchy keep only the Year
5) Create a Slicer again using the TY Date Column and again from the Date Hierarchy keep only the Year
That should do it!
Ideally you should look into adding a Calendar Table!
Let me know if you have any questions.
hi try with this:
Last Year Sales = CALCULATE ( SUM ( Sheet1[Sales] ), FILTER ( ALL ( Sheet1 ), Sheet1[Transaction Year] = MAX ( Sheet1[Transaction Year] ) - 1 ) )
Change the way you calculate the %
You must be doing this => (current / previous ) - 1
Try this instead => (current - previous) / previous
Hope this helps!
EDIT: If this doesn't resolve the issue post the formulas you are using.
Hi @Sean,
Thanks for your reply. My YOY formula is:
YoY Sales Change = ([This Year Sales]-[Last Year Sales])/[Last Year Sales]
Here are the other formulae (all measures):
New Measure --> This Year Sales = CALCULATE(SUM(Table[Sales Amount]))
New Measure --> Last Year Sales = CALCULATE(SUM(Table[Sales Amount]),PREVIOUSYEAR(Table[TY Date]))
New Column --> TY Date = DATEVALUE(Table[Transaction Year])
The "Transaction Year" is a column from the source data which simply contains years (2010 through 2014) and is in "Whole Number" format (after checking the Menu bar --> Modeling --> Formatting).
Not able to understand what's wrong.
Thanks,
dc189
So in the second image in your original post you said you've selected year 2014 in a slicer?
What field are you using to slice and why are the other years still showing if you've selected 2014?
Can you share the pbix file (link to onedrive or similiar)
Hi @Sean,
I want to use "Transaction Year" as a slicer wherein a user can select 2010, 2011, 2012, 2013 or 2014 as values. For e.g. if user selects 2014 the list should only show 2014 and the % growth in 2014 over 2013. I don't want a slicer for "TY Date" as it doesn't look good if you know what I mean. Currently in the second image of my first post, I haven't filtered for any year. I was just giving an example of filtering for 2014.
The problem is when I include "Transaction Year" in addition to "TY Date" in the text chart, all growth % values become infinity. But as soon as I remove it, and keep only "TY Date", I can see the growth %. I guess its because the values in "Transaction Year" are not continuous, but I'm wondering if there's a way around this.
Here's the link to the sanitized dataset: https://www.dropbox.com/s/oaltyf6ihlhh8vj/Book1.pbix?dl=0.
Now I'm facing another problem of including Last Year Sales in the text chart. When I try to include it, the chart does not display the visual.
Thank you, let me know if you have any questions.
dc189
hi try with this:
Last Year Sales = CALCULATE ( SUM ( Sheet1[Sales] ), FILTER ( ALL ( Sheet1 ), Sheet1[Transaction Year] = MAX ( Sheet1[Transaction Year] ) - 1 ) )
Thank you @Sean, your solution has worked! I do agree a calendar table would be useful but I want to keep only one table and make all changes in the form of calculated fields. But thanks anyway.
@Vvelarde Your solution has worked perfectly too! Thank you very much!
However, I'm not able to figure out the total %.
How do I interpret the 367.69%? When the user doesn't filter anything, he/she sees the 367.69% but what does it mean? Below are the actual sales values for 2010 through 2014.
Thank you,
dc189
Hi @Vvelarde,
Thanks a ton! This worked too! I made a couple of tweaks to see show a yoy sales growth chart by channel (online, retail, omnichannel) when any one year is selected and show overall growth chart from 2010 to 2014 when no year is selected. Both charts are superimposed on each other and the hasonevalue is especially useful in hiding one chart and showing the other based on the filter selection again.
One last question I have is: how did you discover the "hasonevalue" function? Is there such a list of advanced functions and their meaning that you can share that might be useful to me?
Thank you,
dc189
hi @dc189
I recommend buy "The Definitve Guide to DAX" by Marco Russo and Alberto Ferrari.
Also follow other pages like
radacad.com
datachant.com
The major issue was the TY Date Column which was not really a date (just year)
Anyway change it like this...
1) TY Date
TY Date = DATE ( Sheet1[Transaction Year], Sheet1[Transaction Month], 1)
2) Last Year Sales
Last Year Sales = CALCULATE ( SUM(Sheet1[Sales]), PREVIOUSYEAR (Sheet1[TY Date].[Date]) )
3) YoY Sales Change
YoY Sales Change = DIVIDE ( [This Year Sales]-[Last Year Sales], [Last Year Sales], 0 )
4) Use the TY Date column in the Table Visual and from the Date Hierarchy keep only the Year
5) Create a Slicer again using the TY Date Column and again from the Date Hierarchy keep only the Year
That should do it!
Ideally you should look into adding a Calendar Table!
Let me know if you have any questions.
Consider implementing it at the table level as part of your data model. Rather than a measurement used in a Visual - instead set it up as a calculated column in a table; both the YOY% and the Year column. Then you'll be able to use that new table in your visual as you seek with a slicer.
Also - Caution on that total % value. I realize it is a default available in the visual - but am thinking it is only accurate if those % values all are based off of the 2010 basis then a total is mathmatically accurate but if they are actually YOY then the basis is changing each year so that a total % has to be calculated separately comparing last year to first year. - - - - unless this embedded feature is more sophisticated than I give it credit.....you might want to double check that manually......
Hi CahabaData,
Thanks for your response. You're right, I could try implementing it at a SQL table/database level instead of doing it in Power BI, but I would like to keep the database as is and have it only have standard columns that can be refreshed. I would like to make all calculations within Power BI. There's no real reason for doing that but it's just a hygiene issue.
Anyway, I'm sure there's a way to do it in Power BI because I've seen a few posts on Last Year Sales calculations although they didn't work for me. Hoping someone from the Power BI community comes up with a Power BI solution soon!
Thank you,
dc189
perhaps there is a misunderstanding; The calculated column approach is within Power Bi - and refreshes.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |