Forum Discussion

vwiles84's avatar
vwiles84
Helper III
6 years ago

Retail data - Last year

I am trying to build a report that shows current sales, budget, target and prior year sales.  The data pulled in via SQL has historical sales data from current to 2014.  As we are dealing with retail data, i need to pull last year sales from the same day of the week from last year (NOT SAME DATE LAST YEAR) but same day of the week.  I have tried to use the sameperiodlastyear DAX command, have built a date table however, I cannot get the report to pull sales of the prior day/year.  How do I do this to make the requirement of same day but not same calendar day?  I believe I just need to tell the column to pull the sales from the sales column but subtract 364. However, I can't get the coding correct.  Any help is greatly appreciated. 

Need Help
Tips and Tricks

7 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi vwiles84 ,

     

    We can try to use the following measure to meet your requirement:

     

    Sales Of Same Week Day Last Year = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        
            'Table'[Date] in SELECTCOLUMNS(DISTINCT('Calendar'[Date]),"D",[Date]-369)
))

     


    If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.


    Best regards,

    • vwiles84's avatar
      vwiles84
      Helper III
      6 years ago

      Thank you for your suggestion.  

       

      For my desired result, I have a table that has columns for Date, actual sales, budget, target, etc.  I need to take the actual sales and reference the date filter for prior year.  I tried to modify the command you suggested, however, It doesn't reference back to the date column to subtract the amount of time to get to the same day last year.  Can I do that or do I need to have a separate date table? 

    • vwiles84's avatar
      vwiles84
      Helper III
      6 years ago

      I got the code to run, however, the result is not the actual sales per location. Rather seems to be a sum total.  Here is the picture of what my results are looking like. I am not able to get it to find the specific locations sales. 

       

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi vwiles84 ,

         

        Sorry for our late reply, Could you please try to use the following measure?

         

        Sales Of Same Week Day Last Year =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table'[Date] ),
        'Table'[Date]
            IN SELECTCOLUMNS ( DISTINCT ( 'Calendar'[Date] ), "D", [Date] - 369 )
    )
)

         


        Best regards,

         

         

         

  • danextian's avatar
    danextian
    Super User
    6 years ago

    Hi vwiles84 

     

    What do you mena by "same day of the week"  previous year? If today is Thursday, Jan 30, 2020, last year's equivalent would be?

    • vwiles84's avatar
      vwiles84
      Helper III
      6 years ago

      The date last year from Thursday 1/30/20 is Thursday 1/31/19.