Automate Trailing 12 months Sales formula

[Rubal_Islam]

Hi All,

I have two formulas that compare the base period (months) with the trailing 12 months of average energy consumption.

Base formula: July 2017 to  June 2018 (this is always static)

Base Energy Usage = CALCULATE(AVERAGE(Data_Source[kWH]),DATESBETWEEN('Date'[Date],DATE(2017,07,01),DATE(2018,06,01)))

Comparison formula: Trailing 12 months. This is not static and always changed. 

Comaprison Energy Usage = CALCULATE(AVERAGE(Data_Source[kWH]),DATESBETWEEN('Date'[Date],DATE(2021,02,28),DATE(2022,01,31)))

In February2022, the trailing 12 months period i am using are Feb2021 to Jan2021.

I am trying to automate the comparison formula so I dont have to update the date range every month.

I have a Date Table and in the Data_Source Table i have a month/year column which are connected.

I am not using any filter to select dates. Trying to make the formula dynamic so i dont have to change it every month.

I am showing the differrence in a chart like below.

Appreciate your help on this guys.

[VijayP · Accepted solution]

@Rubal_Islam 

After Date function where you are getting today's Date subtract with 30 get the previous month Date and it should work

[VijayP]

@Rubal_Islam Share your Kudos as well by clicking on 👍 Icon!

[Rubal_Islam]

Hi VijayP,

The below formula did the trick and it works like magic.

Comaprison Energy Usage v2 = CALCULATE(AVERAGE(Data_Source[kWH]),DATESINPERIOD('Date'[Date],DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-30),-1,YEAR))

Thank you very much.

[VijayP]

@Rubal_Islam 

CALCULATE(Your Measure,DATESINPERIOD(Dates[Date],DAte(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),-1,YEAR))
Try using this function to get the required result and Share your Kudos

[Rubal_Islam]

Hi VijayP,

Thanks for reverting to me. The formula almost works. The only issue is my data is  I need to calculate from Feb21 to Jan22, Not March 21 to Jan22. I have a month's lag on data. I believe the today function for Month and day calculating it from March 22 , -1 year.

Comaprison Energy Usage v2 = CALCULATE(AVERAGE(Data_Source[kWH]),DATESINPERIOD('Date'[Date],DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),-1,YEAR))

 With the Formula, i am getting the below results.

Please Note: 92,664 is the correct result. The Comparison Energy Usage V2 is calulating from Mar21 to Jan22, where as i need to calulate the data from Feb21 to Jan22.

If you please can help how can i change the Month(Today() from Previous Month and Day from Today to Previous Month Day will be greatly appreciated.

Please let me know if I need to clarify any further.