Forum Discussion
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.
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
Any help is appreciated.
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
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")
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.
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.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
DateColumn Column 1 Column 2 Column 3 11/15/2016 1 1 1 11/14/2016 0 2 0 11/13/2016 1 1 1 11/12/2016 1 0 1 11/11/2016 0 1 0 11/10/2016 1 1 1 11/9/2016 1 1 1 11/8/2016 0 2 0 11/7/2016 1 1 1 11/6/2016 1 0 1 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.
