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 

 

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.