cant figure out filter by calculate age

[Anonymous]

I have a date column in a table with a date hierachy (see image).  The date in the column is formated as "Sunday, 12 March 2023"

I want to add some kind of drop down or tick boxes for 30, 60 and 90 days.  I would select 30 and it will show everything older than 30 days from todays date.  I'm not good with the formulas, dax etc so if there is a reaosnably simple way to do this please help.  Or if its a little complicated i'll give it a try but please provide details so i can follow along.

Thanks. 

 

[croberts21]

For this I make an Age column in Power Query. It's pretty easy. First select your date column in Power Query. Then go to Add Column Tab, click Date Dropdown, click Age. 

Now back in PBI Desktop you can make a filter on age, or include the filter in a DAX expression. 

[Anonymous]

Thanks.  This kind of worked.  2 things:

1. In the calculated column the value have no decimal places e.g. whole numbers 30,31,32 etc.  However when i use a standard slicer with the calcualted column the value has 2 decimla places.  How do i get rid of that?

2. In the slicer id i use the list or dropbox option and click 30 i only get things 30 days old when i want everything 30 days or older.  It works using the great than or equal to option but its a bit ugly.  Would prefer click 30 and you get every 30 days or older.

[Arul · Accepted solution]

@Anonymous ,

This is possible via static table approach where you need to create measure for each and every metrics using DAX and map that measures to static table column values 30,60,90 and use this column in the dropdown. 
Refer this blog - https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
But if you are looking for 30, 30-60, 60-90 then you can create the calculated column using the below code.

Dropdown Slicer = 
VAR _today = TODAY()
VAR _today30 = TODAY()-30
VAR _today60 = TODAY()-60
VAR _today90 = TODAY()-90
RETURN SWITCH(TRUE(),
    'Date'[Date]<=_today && 'Date'[Date]>_today30,"Last 30",
    'Date'[Date]<=_today30 && 'Date'[Date]>_today60,"30-60",
    'Date'[Date]<=_today60 && 'Date'[Date]>=_today90,"60-90",
    "More than 90"
    )

Please let me know if it is helping or not.

Thanks,

Arul