Assistance with Sales Comparison by Days of the Week

Power BI
1

Dear,

I need to make a calculation for Retail, where they want to see the sales according to the days of the week compared to the previous year.

For example:
The date is 01.06.2024 and it is the first Saturday (first weekend day) of the month of June.
We want to compare this day with the first Saturday of last year.
If we make the calculation SamePeriodLastYear, it will be based on the date, for example 01.06.2023 was a Thursday, and it is normal that this comparison will not be correct since there are fewer visitors/sales on working days.

If we are based on dates, the comparison should be, compare 01.06.2024 with 03.06.2023 (the first Saturdays of June)

Can you tell me if any of you have this type of request and how you made a solution, or send me a link that will help me in this matter.

Sorry if I posted in the wrong place but I didn’t know how to find any content for this request.

Thank you in advance.

2

Hello,

It would be helpfull if you provided a sample set of data and an output of the wanted result.
I created a random sample data set with sales for each day for two years
then grouped the sales on a weekly basis and for day 5 ( = saturday )

the table visual gives the below screenshot

image
image1173×163 14.7 KB

below the filtered data set for week 22 and day 5 ( saturday )
image
image976×344 28.8 KB

attached my pbix

not sure if this is what you are looking for

kind regards

Roger
retail calculation.pbix (253.5 KB)

3

Thank you Roger,

Based on this, how can we make a calculation with DAX where I would compare this year’s sales with the previous one, for that day.

Thank you in advance.

4

hello,

before creating the DAX measure(s), is the visual giving the expected result ?
is it always day 5 ( saturday ) you want to compare with the previous year for the same weeknr?
can you give a sample data set ,

if you have multiple sales on the same day the grouped table must be changed

kind regards

Roger

5

Hi Roger,

The dataset is usual like the others, I don’t currently have it as it seems, but take the dataset that you attached earlier.

The idea is that when a day is selected, for example Today, it calculates the same day that it was last year based on the week it belongs to.

Example today is 07.06.2024, Friday, which belongs to the second week of June 2024.
We want the Sales (Variance) of Friday of the second week of June 2023.

It doesn’t matter the day since the filter of date will change on slicer.
This type of analysis is used more in Retail, where weekends are compared more than other days, since in the country where I live, Shopping Centers work even on weekends.

As for information, here the week starts on Monday.

Thank you in advance

6

Hello,

attached the same pbix file

I added four measures and deleted the grouped table

hope this is what you are looking for

kind regard,

Roger

retail calculation.pbix (251.5 KB)
image