Forum Discussion

vwiles84's avatar
vwiles84
Helper III
6 years ago
Solved

Prior Year Sales - Filter (Retail comparison)

I have established a connection to a table in SQL that has all of the data I need in my report.  I want to calculate Prior year sales based on a single date filter.  I need the prior year to be the e...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi vwiles84 

     

    Sorry for my late reply. I can't check your sample pbix anymore coz it's expired. 

    I created a sample below to calculate the prior date by conditions for your reference:

    Measure = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[year]=YEAR(MAX('Table'[Date]))-1&&[weeknum]=WEEKNUM(MAX('Table'[Date]))&&[weekday]=WEEKDAY(MAX('Table'[Date]),2)))

     

    and you can calculate the sales by filter the date=[measure].

    Pbix attached.

     

v-diye-msft's avatar
v-diye-msft
Community Support
6 years ago

Hi vwiles84 

 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

 

  • vwiles84's avatar
    vwiles84
    Helper III
    6 years ago

    OK, I have created a PBIX file with the data in question. I am trying to make the prior year sales measure equal the sales from last year (NOT SAME DAY LAST YEAR, but same day of the week regardless of date). 

    Sample PBIX File