How to take an average of last 12 months with the date field

[xaabz]

Hey All,

I am new to alteryx. I am trying to get the average of last 12 months of the date ranges on monthly level. Attached is the sample data.

[RSreeSurya]

Could you please share any sample output, what you are looking for as output , So I can help with the same 

[cjaneczko]

Are you looking for one number that shows the average for all transactions in the last 12 months? See below.

The filter include the following code :

[Date] >= DateTimeAdd(DateTimeNow(),-12,'month')

Summarize tool is set to Average for price. 

[xaabz]

I am not sure how DateTime Add and DateTimeNow functions are working. Do you mind explain to me? Also, if you notice I have Date Year Ranges from 2019 to 2023. How I can get the average of last 12 months and create buckets of each year. For example, Avg of Year 2019 = 44.56% and then Avg of Year 2020 = 46.23% etc

[xaabz]

I am not sure how DateTime Add and DateTimeNow functions are working. Do you mind explain to me? Also, if you notice I have Date Year Ranges from 2019 to 2023. How I can get the average of last 12 months and create buckets of each year. For example, Avg of Year 2019 = 44.56% and then Avg of Year 2020 = 46.23% etc

[xaabz]

This formula only giving the results of last 12 months. I need the records of all past 12 months from the date column. For example if the date is 05/15/2022 then past 12 months date would be 05/15/2021 and then I need to take the Average of the values that falls between 05/15/2021 to 05/15/2022. Does it make any sense?

[xaabz]

I don't know about the output but I put the explanation down below this thread. Please see

[cjaneczko · Accepted solution]

Are you looking for Calendar year averages or rolling 12 month averages? The formula I posted gives you the average of the last 12 months (May 2023 back to June 2022). Can you mock up an output you would like to see as the result?

There is good documentation of the datetime functions in the below link. They explain what each DateTimeAdd and DateTimeNow functions do.

https://help.alteryx.com/20231/designer/datetime-functions

[xaabz]

Thanks much. I figured out the way to solve the problem :)

[RSreeSurya]

Hi,

Attaching the workflow , I have created a macro to consider 12 months rolling for each date and to calculate average for it. 

The date time add helps to identify the rolling 12 months period , it applies -12 months to current date and will give start and end date. Added screenshots of formula and output below for your reference. 

The final output is as shown above, for each date , the output is average of past  12 months data.

Is this the output you are looking for ?