cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brianjsvehaug Frequent Visitor
Frequent Visitor

Page-Level Filter NOW() - 1

I have a page on which I have created a Backlog report for issues in a customer service environment. I'm using TicketCompletionDate and filtering on Blanks. Currently, I have to manually go in and deselect the current date in the page-level filter, otherwise I will get tickets that were just opened 5 minutes ago, as they should not be considered backlog.

 

I have created a measure:

=NOW() - 1 

 

I would like to add the above measure as a page level filter, so that when others view my report, they automatically see the tickets where TicketCreationDate is as of/before 24 hrs ago. This would remove any manual filtering on my part or the part of the end users.

 

Is this something that's possible in PowerBI?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
leonardmurphy Established Member
Established Member

Re: Page-Level Filter NOW() - 1

Nikil (& Idanco) are correct in that you can't just filter by NOW() - 1. The model doesn't know how NOW()-1 is related to TicketCompletionDate. 

 

Nikil's first option (don't bring in the more recent tickets) works if you don't have other reports where you do want to include those tickets. Assuming you do, you have to go for a (calculated) column that you can dynamically filter on. 

 

If you have a date table in your model, you could actually add a calculated column to that table to identify whether the date is before today or not. E.g. =if([Date]>=NOW()-1,"N", "Y"). That makes the "before today" filter available to anything that uses dates in your model, rather than just being limited to tickets. With a date table you can also get more sophisticated and have Y/N columns for MTD, YTD, Last Month, etc. 

 

If you don't have a date table, =if([TicketCompletionDate]>=NOW()-1,"N","Y") will do the same thing, but just for ticket completions.

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.

View solution in original post

6 REPLIES 6
idanco Frequent Visitor
Frequent Visitor

Re: Page-Level Filter NOW() - 1

 

What you can do - is add a calculated column "TimePassed" which will be calculated as Now()-TicketDate

And use this as a filter in report

brianjsvehaug Frequent Visitor
Frequent Visitor

Re: Page-Level Filter NOW() - 1

Idanco - forgive me if I misunderstand you - but I don't see how this would address my issue. NOW() - TicketDate would return different values for each ticket.

 

I have the right calculation (NOW() - 1). It returns exactly what I need, I just need to learn how to make the report only return values that are on or before NOW() - 1.

 

Would love some other suggestions!

nikil Member
Member

Re: Page-Level Filter NOW() - 1

Measures cannot be added to Page filter.

 

Options you have are:

 

  1. Control the data in the Get Data query, to limit it to get only rows that you want into the data model
  2. As previously mentioned, create a calcuated column that will flag all the rows that are not today. Something like  =IF(NOW() - TicketDate)='12/30/1899',"N","Y"). Use this flag to filter the report

 

Ideally, I would go with option 1

Highlighted
leonardmurphy Established Member
Established Member

Re: Page-Level Filter NOW() - 1

Nikil (& Idanco) are correct in that you can't just filter by NOW() - 1. The model doesn't know how NOW()-1 is related to TicketCompletionDate. 

 

Nikil's first option (don't bring in the more recent tickets) works if you don't have other reports where you do want to include those tickets. Assuming you do, you have to go for a (calculated) column that you can dynamically filter on. 

 

If you have a date table in your model, you could actually add a calculated column to that table to identify whether the date is before today or not. E.g. =if([Date]>=NOW()-1,"N", "Y"). That makes the "before today" filter available to anything that uses dates in your model, rather than just being limited to tickets. With a date table you can also get more sophisticated and have Y/N columns for MTD, YTD, Last Month, etc. 

 

If you don't have a date table, =if([TicketCompletionDate]>=NOW()-1,"N","Y") will do the same thing, but just for ticket completions.

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.

View solution in original post

brianjsvehaug Frequent Visitor
Frequent Visitor

Re: Page-Level Filter NOW() - 1

Thank you, Leonard! This has completed resolved my issue. Smiley Happy

dinup Frequent Visitor
Frequent Visitor

Re: Page-Level Filter NOW() - 1

How to make current year as a page level filter

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 60 members 1,250 guests
Please welcome our newest community members: