Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
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.
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?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |