SAMEPERIODLASTYEAR using business days

Hi James, My name is Ron and I am part of the Power Bi team with Jens at STL. Your question was so interesting that Jens and I worked hard to see if we could come up with something more accurate than simply using an Offset of 250. Different years have different amounts of business days in a year, so to use a calculation that retrieves the data from 250 business days ago is not accurate enough. If you are comparing this year's data to last year's data and last year's data is a Sunday then a zero would be returned but it was clear that you did not want that to happen. So we built a solution that gives you a more accurate figure than what you are doing at the moment. The SamePeriodLastYear function only works with sequential date ranges so that one was out the door straight away. You only have business days or working days in your list.
The solution:
1. In Excel you will need to create a dates data table by entering
a heading of Dates2020 and enter the date of 1 January 2020 and you then use the autofill handle (bottom right corner of the cell containing that date) and drag it down until the 31 December 2020.
2. Straight away after you filled down you click the icon at the bottom right of the selection where you will find 'Fill options' and it will have the option 'Fill Series' selected. Choose "Fill Weekdays" instead.
3.In Excel you will need to create a second column in the dates data table with a heading of Dates2019 by entering
the date of 1 January 2019 and you then use the autofill handle (bottom right corner of the cell containing that date) and drag it down until the 31 December 2019.
4.Straight away after you filled down you click the icon at the bottom right of the selection where you will find 'Fill options' and it will have the option 'Fill Series' selected. Choose "Fill Weekdays" instead.

You will now have a table that contains the working days in 2020 and 2019. You will see that the dates are off when there is a weekend in either 2019 or 2020 but it aligns again after the weekends.

5. Open Power Bi Desktop and import the dates data table.
6. Import the data tables containing the figures for 2019 and 2020 you wish to compare against eachother
7. Select Model View in Power Bi Desktop
8. create a relationship between the 2019 table with figures and the dates data table by connecting the 2019 dates field from your table with figures to the dates2019 field in the dates data table
9.create a further relationship between the 2020 table with figures and the dates data table by connecting the 2020 dates field from your table with figures to the dates2020 field in the dates data table

You can now create your visualisations in report view and compare year to year and it will give you the closest business day match in 2019 to your 2020 dates. No DAX needed for this.

I attached 2 files to this forum message. One Excel file gives you an example of the dates data table I discussed and it also has two separate tables, one with 2020 dates and values and one with 2019 dates and values for the same dates a year ago to test with.
The second file is the Power Bi report with the model and a few visuals using the data.

I hope the instructions are clear. At least you will have much more accurate analysis than just going 250 days back.

Try it and if you have any more questions please reply to this forum message.

Kind regards

Ron Oldeboom
Learning and Development Consultant
STL-training