Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

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
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

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

 

Anonymous
Not applicable

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?

 

Hi @Anonymous 

 

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


 

Anonymous
Not applicable

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

Anonymous
Not applicable

Thank you @Mariusz ,

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.