cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

Comparing Last Year Sales with weekend Dates

Hi everyone,

I have been trying for quite some time to figure this out and yet was not successful.

I have 2 simple columns:

1. Sales (the total number of sales for a given date)
2. Weekend (ONLY Sundays, ex. 9/2/2018, 9/9/2018 etc.)

For example, if I need to compare 9/2/2018 sales to last year I would be comparing to 9/3/2017. Meaning same day + 1 but last year.

I have tried multiple functions but none seem to be working unless I put last year's date manually like this:

`LYS = CALCULATE(SUM([SALES]), DATE("9/3/2017"))`

I was trying the code below but I get no results from it:

`LYS = CALCULATE(SUM([SALES]), DATEADD(SAMEPERIODLASTYEAR([Weekend]),1,DAY))`

Has anyone ever dealt with dates like this?

Any help would be greatly appreciated.

2 REPLIES 2
Microsoft Employee

Create a calculated column in sales table.

`weeknum = WEEKNUM([Date],2)`

Then, create below measures to get sales in this year and last year per weekend.

```TY sales = SUM('sales table'[sales])
LY sales =
CALCULATE (
SUM ( 'sales table'[sales] ),
FILTER (
ALL ( 'sales table' ),
'sales table'[Date].[Year]
= YEAR ( TODAY () ) - 1
&& 'sales table'[weeknum] = MAX ( 'sales table'[weeknum] )
)
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

Thank you for your response and your time. I tried your suggestion and I am getting a number but not the one I should be expecting. I believe the problem on my end is that I did not include the rest of my columns on the original question (I thought it wouldn't have an impact).

My table has:

1. Sales
2. Store (number)
3. Department
4. Weekend (Sundays only)

I have filtered out some of the stores with a report-level filter. However, your query seems to bypass that (I have to test the results without filtering).

My goal is to display current sales of each department and each store in a graph. One with the total sales of each department vs last year and one with the stores (same concept).

I also have a weekend slicer so the report should be able to change results according to the slicer.

Thank you so much for your help.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.