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

vs LY Measure Over Multiple Years

Hello,

 

Is there a measure that I can add to the values field to show change over the previous year? I know I can do this with a custom measure for each year, but I would like to see if there's a way without creating a measure for each year.

 

Essentially, what I'm looking for is 2020 Cases divided by 2019 cases in between "2019 Cases" and "2019 Margin"

 

Change vs LY.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: vs LY Measure Over Multiple Years

Hi @NPV32 

 

Sure, you can use the below.

Measure  = 
DIVIDE(
    [Sales], --Your measure or aggregation  
    CALCULATE(
        [Sales], --Your measure or aggregation  
        SAMEPERIODLASTYEAR( 'Calendar'[Date] ) --Date field from date dimension or your calendar table 
    )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: vs LY Measure Over Multiple Years

Hi @NPV32 

 

Sure, you can use the below.

Measure  = 
DIVIDE(
    [Sales], --Your measure or aggregation  
    CALCULATE(
        [Sales], --Your measure or aggregation  
        SAMEPERIODLASTYEAR( 'Calendar'[Date] ) --Date field from date dimension or your calendar table 
    )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

NPV32 Regular Visitor
Regular Visitor

Re: vs LY Measure Over Multiple Years

Thank you @Mariusz ,

 

This is what I exactly what I needed. Appreciate it!

NPV32 Regular Visitor
Regular Visitor

Re: vs LY Measure Over Multiple Years

Hello @Mariusz,

 

The response you provided is what I needed, however, I am running into issues where it is not returning either "-100%" for comparison where previous year has a value and current year does not, and the opposite where it should return "100%" when the previous year has no value and current year does have a value.

 

I know this has to do with division by zero, but haven't been able to correct it yet with either an IF, or IFERROR measure. 

 

Any further suggestions?

 

Super User IV
Super User IV

Re: vs LY Measure Over Multiple Years

Hi @NPV32 

 

Try something like 

Sales Year vs Last Year = 
VAR _sales = [Sales] 
RETURN 
IF( 
    ISBLANK( _sales ), 
    -1,  
    DIVIDE( _sales, _sales - [Sales Last Year] ) 
)

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski


 

NPV32 Regular Visitor
Regular Visitor

Re: vs LY Measure Over Multiple Years

@Mariusz This did what I was looking for. Thank you again!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors