Forum Discussion

vwiles84's avatar
vwiles84
Icon for Helper III rankHelper 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...
Need Help
Tips and Tricks
v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity 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
Icon for Helper III rankHelper 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?