cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fletch8527
New Member

Relative Date X Days Before

I'm very new to Power BI, so please be kind. I searched the board first but didnt find a similar post. Im playing around with a very basic report that gets data from our Active Directory. One column is a date the other is the user's display name. Is there a way to use the Relative Date filter/slicer to show only records that are older than X number of days from today? I can get a list of records from the last X days, but I am more concerned with records that older than lets say 30 days.

1 ACCEPTED SOLUTION

Accepted Solutions
Chihiro Solution Sage
Solution Sage

Re: Relative Date X Days Before

I'm guessing that you have Date column in Datetime format. In "M", it is important to match data type for both left and right of the operator.

 

Then change it to...

= Table.SelectRows(#"Changed Type", each [Date] < Date.AddDays(DateTime.LocalNow(),-30))

View solution in original post

5 REPLIES 5
petrovnikitamai Resolver V
Resolver V

Re: Relative Date X Days Before

new column = IF(DATEDIFF(AD[date];TODAY();DAY)>30;AD[user];BLANK())

try to add new column.

 

if u want dinamicaly value instead 30 try to use what/if parametr

Highlighted
Chihiro Solution Sage
Solution Sage

Re: Relative Date X Days Before

I'd do this in query editor.

 

Once data is brought in, apply 'date is before' filter using any date.

 

Then in advanced query editor, find the Filtered row step just applied.

It will look like...

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] < #date(2018, 10, 1))

Now replace #date() part with dynamic calculation.

Something like....

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] < Date.AddDays(DateTime.Date(DateTime.LocalNow()),-30))

 

Edit: You can parameterize -30 part if desired.

fletch8527
New Member

Re: Relative Date X Days Before

I tried your suggestion and got this error

Expression.Error: We cannot apply operator < to types Date and DateTime.
Chihiro Solution Sage
Solution Sage

Re: Relative Date X Days Before

I'm guessing that you have Date column in Datetime format. In "M", it is important to match data type for both left and right of the operator.

 

Then change it to...

= Table.SelectRows(#"Changed Type", each [Date] < Date.AddDays(DateTime.LocalNow(),-30))

View solution in original post

fletch8527
New Member

Re: Relative Date X Days Before

Awesome, Thanks! I was just reading about that and noticed I had a DateTime field. Thanks for the fast replies. Still trying to get my head wrapped around this stuff. So much more involved that a tabel in Excel 😉

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors