1 / 11

Year on Year Comparison in Power BI

Year on Year comparison by weekday in power BI<br><br>A Step by Step guide to avoid potential errors when using SAMEPERIODLASTYEAR and a simple solution to ensure you compare matching weekdays<br><br>https://www.selectdistinct.co.uk/2024/04/16/year-on-year-power-bi/<br><br>#PowerBI #SAMEPERIODLASTYEAR #DataViz

Simon52
Download Presentation

Year on Year Comparison in Power BI

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Comparing YEAR on YEAR in Power BI Power BI Tips and Timesavers https://www.bensound.com/ (energy) https://www.bensound.com/

  2. Power BI has a built in function SAMEPERIODLASTYEARBut, it does not necessarily give you what you need, here we show an alternative to make sure year on year comparisons match by weekday

  3. Why would you need to SAMEPERIODLASTYEAR does not take the day of the week into accountAnd this year is a leap year, so the dates are two days out of sync

  4. The Simple Solution We will use a matching date from 364 days earlier for comparison. Then each weekday will always match even across a leap year

  5. Use Case Example Data that varies between weekdays and weekends need to have sensible comparisons, here we can see website impression which drop at the weekends

  6. Step by Step Solution The first thing we will do is show what happens when we use the same period last year functionWe create a measure to return the Impressions for the previous year using the SAMEPERIODLASTYEAR function Impressions LY = CALCULATE(sum('Sample GSC Data'[Impressions]), SAMEPERIODLASTYEAR('Sample GSC Data'[Date])) Then we can easily add the result into a simple table to see the result

  7. Step by Step Solution At first glance this looks OKBut validation the data back to prior year we can that SAMEPERIODLASTYEAR is not working as we would like it toThe standout number for last year is the 13 showing against the 13th of April being compared to Saturday the 13th of April 2024Looking back at the 13th of April 2023 we can see it was a Thursday

  8. Step 2 – Create a Measure to return the correct Year on Year comparison We know that if we compare to 364 days ago we will always match the weekday. So we create a filtered measure that does this Impressions LY2 = CALCULATE(sum('Sample GSC Data'[Impressions]), DATEADD('Sample GSC Data'[Date],-364,DAY)) We use the DATEADD function, with a minus 364 days value to match to the correct dates it now correctly aligns to the week day

  9. If you need to have comparison of year on year performance where the data is by day, then you need to use this technique or similar Subscribe to our channel to see more tips and timesavers

  10. For more Tips, Tricks and Timesavers, visit our websiteBusiness Analytics Blog – Select DistinctCredit: simon.harrison@selectdistinct.co.uk

More Related