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

dynamic rolling date (last 7 days)

I am simply trying to put a dynamic date filter to show me a filtered result of this table of the last 7 days (Test Date) everytime this report is opened.

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The visual filter doesn't have a function for last x days.

I tried multiple things, including the Filter function, Measures but not working.

 

I even tried calculated date columns with filter but not working

Capture2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

Any help is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
th3h0bb5 Regular Visitor
Regular Visitor

Re: dynamic rolling date (last 7 days)

Hey Robertocarlos,

 

My apologies, I had the less than symbol backwards. It should be <=7 instead of >=7.

 

Use that calculated column and place it as a page-level filter with it's value set to 'Yes'. This should filter out everything except rows where the Date Column is in the last 7 days.

7 REPLIES 7
PatrickPilz Frequent Visitor
Frequent Visitor

Re: dynamic rolling date (last 7 days)

I don't see you calculating a measure. FILTER returns a table and could not be used as it does not return a scalar value in the filter context. I had a similar problem, where I needed the past 5 weeks, and used the CALCULATE function like:

 

SumKGpast5Weeks:=CALCULATE(Sum([QuantityWeight]),DimDate[WeekBeginDate]>=TODAY()-42)

In this case, I needed always the totals for the past 5 weeks from the beginning of the week. So I created a column with the week-begin for each date. You can of course change this to your expression, but you need to have a measure calculated.

th3h0bb5 Regular Visitor
Regular Visitor

Re: dynamic rolling date (last 7 days)

Use the DATEDIFF function and an IF statement. This will give you a "Yes" if the date is within the last seven days and a "No" otherwise.

 

lastSeven = IF(DATEDIFF([Date],TODAY(),DAY)>=7, "Yes", "No")

robertocarlos Frequent Visitor
Frequent Visitor

Re: dynamic rolling date (last 7 days)

For some reason it didn't work.

 

I tried the following formuila below to get only the dates between last 5 days,

TestDate Caluclated Column = DATESBETWEEN(table[TestDateTime (bins)],(TODAY()-5),TODAY())

 

but it would give me an error

A table of multiple values was supplied where a single value was expected.

 

 

I am simply trying to find a way so when the report is opened it defaults the date to last 5 days

DateColumnColumn 1Column 2Column 3
11/15/2016111
11/14/2016020
11/13/2016111
11/12/2016101
11/11/2016010
11/10/2016111
11/9/2016111
11/8/2016020
11/7/2016111
11/6/2016101
th3h0bb5 Regular Visitor
Regular Visitor

Re: dynamic rolling date (last 7 days)

Hey Robertocarlos,

 

My apologies, I had the less than symbol backwards. It should be <=7 instead of >=7.

 

Use that calculated column and place it as a page-level filter with it's value set to 'Yes'. This should filter out everything except rows where the Date Column is in the last 7 days.

robertocarlos Frequent Visitor
Frequent Visitor

Re: dynamic rolling date (last 7 days)

Thanks th3h0bb5,

 

It worked.

 

I wonder though, if there is another way to use the Date Filter directly.. but Thanks so much Smiley Wink

TristanKuesters Regular Visitor
Regular Visitor

Re: dynamic rolling date (last 7 days)

I also looking for a solution to find a rolling date for my report for the last month.

 

I want to share my solution. It is a calculated column on the date table.

 

IsLastMonth = IF('Calendar'[Month]=MONTH(today())-1 && 'Calendar'[Year]=YEAR(TODAY());"Yes";"No") 

 

And a page/report filter to YES for this column.

elatreille Regular Visitor
Regular Visitor

Re: dynamic rolling date (last 7 days)

Are you using a measure or a column?

I am trying to display the last 2 days data so data from yesterday and the day before. I am using a measure as is:

last_2_days = IF(DATEDIFF('Date'[Date],TODAY(),DAY)<=2, "Yes", "No") but having an issue with this field 'Date'[Date].

It returns a single value for column date in table 'Date' cannot be determined...

So if I understand the formula you posted here it looks between 2 dates and if the difference is lower than 2 it will return YES and under the filter you add it there and if the value is Yes than the data will show.

 

So I have a date table with a relationship to my impression table to the date field. I want to display the total impressions from 2 days before today... (yesterday and the day before)

 

How do I do that?