Keep original filter context at moving average

[Anonymous]

Hi folks,

following issue:

I've got a KPI rendered in a weekly report. That was the easy part. It's displayed for all products that has been produced in the week of interest.

Now, to allow for judgement on KPI I like to compare it to a 12MTD average of the same KPI.

For this, I've used the known DATESINPERIOD in a CALCULATE on the previous efficiency measure:

Eff% 12MTD = CALCULATE([Eff%];DATESINPERIOD(Dates[Date];LASTDATE(Dates[Date]);-1;YEAR))

However, as soon, as I put this into a visual, all products show up - instead of just the ones of the respective week (see above).

What can I do to keep the filter on my products?

I tried various options on ALLEXCEPT / KEEPFILTER but was not successful.

Hope I can learn from you....

Thanks in advance.

[Anonymous]

Hi @Anonymous,

Current dax formula not directly calculated on summarized/grouped level when you nested multiple measure formula, it will drill and calcualte on detail row level, then summary these result instead direct calculate on grouped level.

For this scenario, you need to add condition to check summary level and write a specific formula to manually summary records to get calculation result on total level.

Time Intelligence Without Date Columns in DAX

Optimizing DAX expressions involving multiple measures

Regards,

Xiaoxin Sheng