Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello! I'm looking to create a rolling twelve month count of activities.
I have a table with the form:
"Record Date", "Activity Type", "Month - Year", "Termination Count"
1/1/2020, Termination, Jan - 2020, 1
1/2/2020, Termination, Jan - 2020, 1
1/15/2020, Termination, Jan - 2020, 1
2/2/2020, Termination, Feb - 2020, 1
6/15/2020, Termination, Jun - 2020, 1
and so on.
I am able to build a bar chart that shows me how many Terminations happened in a given "Month - Year" by using the measure:
Sum([Termination Count])
I want to build a bar chart that shows me the Termination Count for a rolling 12 month period.
For example, I would want the "Dec 2020" bar to be the sum of all terminations that happened from the end of December 2020, to the beginning of January 2020, and I would want the "Nov 2020" bar to be the sum of of all Terminations that happened from the end of Nov 2020 to the start of December 2019.
I've attempted this:
Sum({$<[Record Date]={'">$([1 Year Before Month End Date])"'},[Record Date]={"<=[Month End Date]"}>}[Termination Count])
Where [Month End Date] is the end of the Record Date's Month, and [1 Year Before Month End Date] is one year prior to [Month End Date], but I only get 0's when I try this.
Thank you! Any assistance is appreciated!
I have found the easiest solution to a rolling sum is Henric's As-Of-Table
I have found the easiest solution to a rolling sum is Henric's As-Of-Table
Thank you for the suggestion! I'm taking a look now.
This has worked wonderfully, thank you for the link. It took a minute to wrap my head around but this really seems like a graceful solution.