Dynamic dates

[Sakaraipandian]

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.

1. How to implement n thks in advance

[parry2k · Accepted solution]

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

[Sakaraipandian]

Is it work for every year 

[parry2k]

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

[Sakaraipandian]

S right

[parry2k]

@Sakaraipandian what festival date has to be with the last 30 days?

[Sakaraipandian]

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.