Forum Discussion
Dynamic dates
Hai,
My question is, for every year I have a calendar table with festival name and date columns, in every year festival date will vary then how to calculate sales with last 30 days , festival name is constant but date will change every year i.e one month before or after.
For eg. Festival name deepavali. In year 2020_ Nov 14th, and in year 2021_Nov 4th. With corresponding festival name I want to calculate the last 30 days of the sales.
- How to implement n thks in advance
Sakaraipandian you can create another table that contains the festival date and name of the festival and then use that as a slicer for users to select the festival, and then use the date from the festival date to get the sales for the last 30 days.
Last 30 Days Sale = VAR __festivalDate = MAX ( YourFestivalTable[Date] ) VAR __startDate = __festivalDate - 30 RETURN CALCULATE ( [Sales Measure], DATESBETWEEN ( YourCalendarTable[Date], __startDate, __festivalDate ) )You might need to tweak it a bit but this will get you started.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
6 Replies
I did mention on post itself, the festival name is same but every year date will change acc to that I have to calculate last 30 days sale from that date For eg. Festival name deepavali. In year 2020_ Nov 14th, and in year 2021_Nov 4th. With corresponding festival name I want to calculate the last 30 days of the sales.
Sakaraipandian you never mentioned the last 30 days from what date? I guess you are saying you want the last 30 days from the festive name? Is that what you are looking for?
S right
Sakaraipandian you can create another table that contains the festival date and name of the festival and then use that as a slicer for users to select the festival, and then use the date from the festival date to get the sales for the last 30 days.
Last 30 Days Sale = VAR __festivalDate = MAX ( YourFestivalTable[Date] ) VAR __startDate = __festivalDate - 30 RETURN CALCULATE ( [Sales Measure], DATESBETWEEN ( YourCalendarTable[Date], __startDate, __festivalDate ) )You might need to tweak it a bit but this will get you started.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Is it work for every year
