Dynamic Date Filtering

penguin_96 · ‎04-28-2023

Hi All
New to power BI and am trying to build a report. I am build a marketing dashboard. It has slicer values google, Facebook, BD, Non-BD etc.

below it I have a matric table wherein I have added month_year in rows, r_value (its basically numbers from 1 to x signifying number of months after month year value) and values in the data.

I have to filter this data. As of now I am using the filter pane adding month_year to it and opting for relative date to filter the values for the last 8 years. The data being shown after this is perfectly correct. Now the issue is I want to see last 8 years of data for google BD and Non-BD but not for facebook. I was to see last 2 years data for facebook. Also, more data will be added in table and overall slicer like whatsapp, appnext etc which too would require filtering of 1 yrs or 2 yrs. Is there a way to dynamically filter this.

I tried this measure:

Avg_Exits %% =
VAR year_number = [Years to Filter] (a column I have created that gives numerical value when a slicer value is selected indicating number of years)

return
CALCULATE(AVERAGEX(VALUES(Exits[month_year]),[Exits%]), DATESBETWEEN(Exits[month_year], DATE(YEAR(TODAY())-year_number,MONTH(TODAY()),DAY(TODAY())),TODAY()))

and it is working perfectly well for basic table. In case of matrix similar approach is not working. It is filtering my column values but not my row values. This is the measure I tried

Exits %% =
VAR year_number = [Years to Filter]

return
CALCULATE(DIVIDE(SUM(Exits[exits]), CALCULATE(SUM(Exits[exits]), exits[r] = 1)), DATESBETWEEN(Exits[month_year], DATE(YEAR(TODAY())-year_number,MONTH(TODAY()),DAY(TODAY())),TODAY()))

Any advice on what can be done?