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.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

Last year the same weekday is 364 days behind. So as long as you view a Day or view by week this formula should work.

Week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

  • vwiles84's avatar
    vwiles84
    Helper III
    6 years ago

    When i add that command, it is pulling the exact same number as this year sales. It's not bringing up last year.

     

    What do you mean when you said this: Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.? 

     I have the date table and it has a one to many relationship to my sales data table.