Filter a report using query string for relative dates

[Anonymous]

Hi,

Is there a way pre-filter dates? I want my data to get filtered by rolling 30 days. 

I want the user to have an option to change the filter if needed and look for broader time period. But by default when the user opens the link he should see only 30 days since those are most relevant. 

My report is automatically refreshed everyday.

Regards

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

How about to use filter on this visual?

Days = 
VAR x = 
CALCULATE(
    LASTDATE('Table'[Date]),
    ALL('Table'[Date])
)
RETURN
DATEDIFF(
    MAX('Table'[Date]),
    x,
    DAY
)

Even if your data is updated daily, only the last 30 days will be displayed.

If you want to display longer data, you need to modify the filter.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Aron_Moore]

Sure. I have a few reports that filter "Current Month" or "Current Quarter".

I just build a custom column that checks like

IF('Executive Report'[Fiscal year / Qtr]=YEAR(TODAY())&" - Q"&ROUNDUP(MONTH(TODAY())/3,0),"Yes","No")

Then I just save the report with the filter selected to "Yes".

[Anonymous]

Thanks! Can this filter be removed by the customers later, if they want to expand the date field? 

[Aron_Moore]

Yup. The filter visual looks like this:

They can just clear the filter and it'll open the report to all month, quaters, etc.

[Anonymous]

I see! But you have made buckets in your time. Mine is a date slicer like below.  I want to provide 30-day dynamic filter.

[Aron_Moore]

Yes, I made buckets and that's what makes it dynamic.

Whenever the report is refreshed the calculated column is recalculated potentially moving the current quarter.

You'll need to do something similar and switch from the Date slicer to a 'regular' slicer to handle the dynamism.

[Anonymous]

But buckets don't serve our business purpose. We need to drill down to the date level in historical data too.

If I have both bucket and date filter, the User Experience is really bad.

Since date selector shows different dates and the bucket filter shows something else. 😞