How to calculate historical averages from current slicer

[Deermeat]

Hello,

I'm currently trying to find a way to calculate the past averages for different categories based on slicers chosen. The graph is created using Charticulator as it has categorical data on the X-Axis called Identifier and numerical data on the Y-Axis called Score.

Each dot in the graph below is 1 data point for that identifier, and there can be multiple identifier for each of them. 

The graph also has a date slicer and a few other catogories slicer as well (I've shortened it to only two for simplicity). The data is confidential, so I have edited out the picture to conceal certain details. Please let me know if more information is needed.

Current Graph:

I'm trying to make Charticulator automatically show historical averages based on the date and categories that is chosen on the slicer.  So if Feb 2022, currency as AUD and country as AGO is chosen, I wish to have the score averages for each of the X-axis categories (A-R) for 6 months ago at Aug 2021 and 3 months ago at Nov 2021 with both currency as AUD and country as AGO. This will then be inserted into the charticulator to be displayed as extra dots.

Desired Outcome: 

From my understanding, the basic process of how to do this is broken down into:
1. Know that Feb 2022, AUD, and AGO has been chosen.

2. Go to 6mths/3mths back, while knowing that information from other slicers are needed (currency as AUD and Country as AGO).

3. Find the score averages for each identifier. 

4. Insert the score averages into Charticulator as another dot to be shown everytime a slicer is updated. 

I'm currently stuck at the first step of how to identify which date and slicers have been chosen, but I'm also quite confuse on how to make sure to keep the slicers in mind when going back in time and looking for the data to find averages. 

I have uploaded a sample workfile with the data anonymized. Country and Currency has been converted to between 1-9 as well.

https://drive.google.com/file/d/1oi6wAngNrKdzf2p1yXW5B-XGnzeVtYDx/view?usp=sharing

Thank you for your help

DeerMeat

[Deermeat · Accepted solution]

For anyone else that come across this:
Managed to get it to work with help from @hnguy71

Link here: https://community.powerbi.com/t5/Desktop/How-to-subtract-3-months-from-a-Date-then-find-nearest-poss...

[v-luwang-msft]

Hi @Deermeat ,

Could you pls share your pbix file ,and remember to remove confidential data.

Best Regards

Lucien

[Deermeat]

Hi @v-luwang-msft,

Thank you for the reply! I have uploaded the .pbix file to google drive with anonymized data. It can be found here: https://drive.google.com/file/d/1oi6wAngNrKdzf2p1yXW5B-XGnzeVtYDx/view?usp=sharing

Due to the lack of data, I just realize that finding 1 year ago average is not possible. I think it make more sense for 6 months or 3 months ago!

Thank you very much for your help!