Forum Discussion
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 exact day of the week as last year not necessarily the same date. For example last year from today's date (Tuesday 2/11/2020) is Tuesday 2/12/2019. In my table, there is daily sales data starting from 2014 up to current. I have created another date calendar table in Power BI that does a calculation to determine what the last year's date is. What I cannot get it how to write the calculation to pull in the sum of sales based on the DayLY column in this calendar table. Any ideas?
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.
5 Replies
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 LinkedinWhen 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.
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.
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).
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.
