What's the best way to calculate the average of previous 4 months data excluding current month?

I have a visual say looking at April only (Filtered by using a calculated field - minOver(min({TRANSACTION_DATE}), [{TRANSACTION_DATE}])) and added to a filter to show relative dates > this month

This shows only current month’s view.

I have another calculated field called last_4_months_average_delta which averages the difference of 2 values as a percentage from another calculated field.

My problem is when I use the ast_4_months_average_delta field in the visual, instead of picking only the last 4 months, it’s picking current month also.

How do I make it ignore the current month and take only previous 4 month average?

@rumesh Create calculated field in dataset to check the date, if date is current the set value or if its not current month then set value to zero. This give an extra column where you have values for current and zeros for other.
When you calculating the average, use the calculated field from dataset to negate the value, which will drop current month value and you can perform average for last 4 months (excluding current).

1 Like

Current month is based on a parameter date. It’s not using now(). So will it still work from dataset level? I have never tried that.

Hi @rumesh,
It’s been awhile since last communication in this thread. Checking in to see if you had any additional questions or if you were able to find a work around for your case?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

Hi @rumesh,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!