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)

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

5 Replies

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

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

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