Forum Discussion

alexgrammenos's avatar
alexgrammenos
Regular Visitor
7 years ago

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: Sales (the total number of sales for a given date) Weekend (ONLY...
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi alexgrammenos,

 

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

alexgrammenos's avatar
alexgrammenos
Regular Visitor
7 years ago

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.