Forum Discussion

Rubal_Islam's avatar
Rubal_Islam
Icon for Helper II rankHelper II
4 years ago
Solved

Automate Trailing 12 months Sales formula

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) Ba...
Need Help
  • VijayP's avatar
    VijayP
    4 years ago

    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's avatar
VijayP
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
Rubal_Islam
Icon for Helper II rankHelper II
4 years ago

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.