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:

  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

  • 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.