cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
zflory Regular Visitor
Regular Visitor

Re: Year over Year Comparison

Thank you for the reply again @nickchobotar, but I do not have a date column in either of my 2016 or 2017 tables. Do those need to be added?

 

powerbi3.PNG

nickchobotar Established Member
Established Member

Re: Year over Year Comparison

@zflory

 

Interesting dataset. You still need one table, no point in have two separate tables with the same field and data types.

I recommend to add a Year field to each table. 2016 table gets 2016 value for every row and 2017 table get 2017 value for every row.

 

You can do this in Power Query

Under the Add Column tab, click Custom Column and add  "=2016"    this will add column with 2016 value for every row in your 2016 table. One the home tab, under transform please convert  the new Year column to a whole number

Repeat the same operation for 2017 year.

 

Create a new blank query and enter the following code = Table.Combine({#"2017", #"2016"}). This will create a new table which is a union of 2016 and 2017. Click Close and Apply to move your changes into the model.

 

 

image.png

Now, you can use your newly created field Year as slicer and write DAX to get that YoY metric.
Something like this should work

 

 

YoY Value = 
VAR Value2016 = CALCULATE(SUM(Combined[One ]), Combined[Year] = 2016)
VAR Value2017 = CALCULATE(SUM(Combined[One ]), Combined[Year] = 2017)
RETURN
Value2017 - Value2016  
	

I would recommend to capture dates in your system for your trnasactions too. Not just years.

 

N -

 

zflory Regular Visitor
Regular Visitor

Re: Year over Year Comparison

@nickchobotar thank you, this helped immensely. Is there a way to show negative numbers using this measure? Some of my year to year comparisons are negative but all of the numbers this measure returned are positive

 

powerbi6.PNG

nickchobotar Established Member
Established Member

Re: Year over Year Comparison

Hi @zflory

 

The reason you are having negative numbers is because your 2017 aggragated volume for some items is less than your 2016.

 

(which makes perfect sense since 2017 is not over yet. Technically, you are comparing 12 months of 2016 with almost 11 months of 2017

 

image.png

 

Hopefully, this was helpful.

 

Nick -

 

zflory Regular Visitor
Regular Visitor

Re: Year over Year Comparison

@nickchobotar thank you again for your assistance. Is there a way to do a YTD comparison? Like if I only want to do a comparison of the data from YTD-september of 2016 and 2017. I would rather not have a full 12 months worth of data from 2016 if I'm only comparing through September 2017.

 

Again thank you for all of your help 

nickchobotar Established Member
Established Member

Re: Year over Year Comparison

@zflory

 

With your data this is not fully possible. We need dates or months and you have neither. So, the only thing I can think of is to use a year fraction.

 

What I am saying below is calculate year fraction of 2017 YTD which is about 80% and them use it to adjust your 2016 volumes by the same percentage.  Now, you should be comparing apples to apples.

 

YoY Value =

VAR YearFraction = YEARFRAC(DATE(YEAR(TODAY()), 1, 1), TODAY())  // Get Year fraction for 2017

VAR Value2016 =
    CALCULATE ( SUM ( Combined[One ] ) * YearFraction , Combined[Year] = 2016 )  // adjust 2016 volumes to 2017 through year fraction adjusting factor 

VAR Value2017 =
    CALCULATE ( SUM ( Combined[One ] ), Combined[Year] = 2017 )
RETURN
Value2017 - Value2016

 

- Nick

zflory Regular Visitor
Regular Visitor

Re: Year over Year Comparison

 thank you @nickchobotar. Can you see the below volumes? The difference between them is shown on the right (25966). Is there any way to display negative values for the 2017 data - 2016 data? I want it to show -25966, not 25966

 

powerbi7.PNGpowerbi8.PNG

nickchobotar Established Member
Established Member

Re: Year over Year Comparison

@zflory

 

If I am understanding correctly, you are trying to force your numbers to be negative. 

If so, then you can use a minus in front of your numbers wrapped in absolute function, as shown below:

 

YoY Value =

VAR YearFraction = YEARFRAC(DATE(YEAR(TODAY()), 1, 1), TODAY())  // Get Year fraction for 2017

VAR Value2016 =
    CALCULATE ( SUM ( Combined[One ] ) * YearFraction , Combined[Year] = 2016 )  // adjust 2016 volumes to 2017 through year fraction adjusting factor 

VAR Value2017 =
    CALCULATE ( SUM ( Combined[One ] ), Combined[Year] = 2017 )
VAR Result = - (ABS(Value2017) - ABS(Value2016)) RETURN Result

 

Did I understand you correctly? Do you want turn all numbers into negative or just turn the positive numbers into negative?

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 270 members 3,122 guests
Please welcome our newest community members: