cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Show the date for previous year revenue in a specific column

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 🙂 

1 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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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:

Mad_0-1596704925876.png

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors