Show the date for previous year revenue in a specific column

[Mad]

Hi there,

I have built a report with daily revenue and added a measure representing the change as compared to the same day of the week of the previous year. However, I need to add a column that actually shows which days are being taken in account for the previous year.

So for instance I would have Saturday 1st of August 2020, Saturday 3rd of August 2019, and then the measures I created.

I am using a standard European calendar. Which function should I use to display these previous year dates?

Thanks in advance 🙂 

[amitchandak]

@Mad , typically same weekday last year is 364 days behind. Use with a date table.

example

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

Check

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Mad]

Hi @amitchandak,

wow, lots of great stuff here! Thanks for all of this.

The calculation with -364 days works fine. I need to add a column with the date of comparison for reference.

This is what I want to achieve and currently have in Excel:

It should be possible to make a calculated column in my calendar table with the same rule (-364 days) and then add it to the report. But the formula I use doesn't work 

Date PY= CALCULATE(Datetable[Date];-364;DAY)

Where is my reasoning mistake?

[mahoneypat · Accepted solution]

Since it's a calculated column, you can just use

Date PY = Datetable[Date] - 364

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat