Average of last 12 months data (not working as expected)

[pedrohenriqueperna]

Hi,

I'm trying to make a KPI to show the average of customer returns in the last 12 months considering the max data filtered.

For instance, if the user has selected March of 2024, then the displayed value should be the average of returns per customer since March of 2023 (12 months back).

Basically I counted the distinct sales id aggregated by customer and then used avg(). It seems to be working as expected while selecting a single year, but if I select 2023 + 2024 the value changes for some reason

The expression I'm using is the following:

Avg(Aggr({1} Count(Distinct {1<sale_id= P({1<customer_days_return= {"*"}, sale_date= {">=$(=AddYears(Max(sale_date), -1))"}>}), key_customer -= {'1'}, customer_days_return-= {'0'}, sale_date= {">=$(=AddYears(Max(sale_date), -1))"}>} sale_id), key_customer))

I haven't validated the result yet, but the number seems correct for the single selection. I added {1} to ignore the filters, but still it responds.

Could anyone help me out?