cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dc189
Helper II
Helper II

YoY Growth % disappears when changing from Date to Year column in Direct Query model

Hello,

 

This is what I've created so far: 

 

1.JPG

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:

 

2.JPG

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!

 

3 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

@dc189

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! Smiley Happy

Ideally you should look into adding a Calendar Table!

Let me know if you have any questions.

View solution in original post

Vvelarde
Community Champion
Community Champion

@dc189

 

hi try with this:

 

Last Year Sales =
CALCULATE (
    SUM ( Sheet1[Sales] ),
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[Transaction Year]
            = MAX ( Sheet1[Transaction Year] ) - 1
    )
)



Lima - Peru

View solution in original post

Vvelarde
Community Champion
Community Champion

@dc189

My option would be don't show a total of %yoychange. To do that modified the measure adding this: If(Hasonevalue(Table[transactionyear]), TheFormula)



Lima - Peru

View solution in original post

14 REPLIES 14
Sean
Community Champion
Community Champion

@dc189

Change the way you calculate the %

You must be doing this => (current / previous ) - 1

Try this instead => (current - previous) / previous

Hope this helps! Smiley Happy

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

Sean
Community Champion
Community Champion

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

Vvelarde
Community Champion
Community Champion

@dc189

 

hi try with this:

 

Last Year Sales =
CALCULATE (
    SUM ( Sheet1[Sales] ),
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[Transaction Year]
            = MAX ( Sheet1[Transaction Year] ) - 1
    )
)



Lima - Peru

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 %.

 

Capture.JPG

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.

 

Capture2.JPG

 

Thank you,

dc189

Vvelarde
Community Champion
Community Champion

@dc189

My option would be don't show a total of %yoychange. To do that modified the measure adding this: If(Hasonevalue(Table[transactionyear]), TheFormula)



Lima - Peru

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

Vvelarde
Community Champion
Community Champion

hi @dc189

 

I recommend buy "The Definitve Guide to DAX" by Marco Russo and Alberto Ferrari.

 

Also follow other pages like 

 

https://powerpivotpro.com

 

https://www.sqlbi.com

 

radacad.com

 

datachant.com

 

 




Lima - Peru

Thanks a lot @Vvelarde!

Sean
Community Champion
Community Champion

@dc189

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! Smiley Happy

Ideally you should look into adding a Calendar Table!

Let me know if you have any questions.

CahabaData
Memorable Member
Memorable Member

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

 

 

www.CahabaData.com

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

CahabaData
Memorable Member
Memorable Member

perhaps there is a misunderstanding;  The calculated column approach is within Power Bi - and refreshes.

 

 

www.CahabaData.com

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors