cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gjadal Member
Member

Date filter

What is the best practice to add From date and To Date filters on my report and default them to a week?

6 REPLIES 6
Super User
Super User

Re: Date filter

I don't think there is one best way. How about using the date timeline slicer on you calendar date table and set it to weeks. 

https://powerbi.microsoft.com/en-us/blog/visual-awesomeness-unlocked-the-timeline-slicer/

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: Date filter

@gjadal - Can you add a little detail to this, are you trying to default the selection to the current week?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

gjadal Member
Member

Re: Date filter

Basically I want a from date control and a To date control where the user can pick dates to filter the data.

By default I want the dates to be 1 week apart.

I tried using the time slicer but that doesnt have weeks as an option and its not very elegant..

 

Super User
Super User

Re: Date filter

I don't know of any way to set a "default" period apart.  You could set a start date and a "number of weeks" slicer and set that to 1. Then the user could change it from 1 week to anything else. Just an idea. 

 

Or you could create 2 calendar tables, one for the FROM and one for the TO date. 

each table should have a single column that contains the week date you want, eg week commencing dates or week ending - what ever you want. 

don't join them to anything 

write 2 harvester measures to "harvest" what has been selected. 

 

From Date=max(FromCal[Date])

To Date = max(ToCal[Date])

 

write the a measure that reacts to the slicers. Something like this. 

 

total sales selected period =

   CALCULATE (sum(Sales[qty]),

    filter (Sales,

        Sales[date] >= [From Date] &&

        Sales[Date] <= [To Date]

     )

)

 

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
greggyb New Contributor
New Contributor

Re: Date filter

The advanced filtering options allow you to choose a start and end date for any date field. A single date dimension will do. You can save any literal filter criteria with the report.

 

Filters.

AlexSavchenko Frequent Visitor
Frequent Visitor

Re: Date filter

Hi, my solution for the similar case:

 

I wanted to compare last 7 days with previous 7 days (not calendar week!).

For that in the Calendar table (dimdate), I have created calculated field with formula:

if(dimdate[Date]<TODAY() && dateadd(dimdate[Date],8,day)>TODAY(),

       8-DATEDIFF(dimdate[Date],TODAY(),day),

       if(dateadd(dimdate[Date],8,day)<=TODAY() && dateadd(dimdate[Date],15,day)>TODAY(),

              8-(DATEDIFF(dimdate[Date],TODAY(),day)-7),

              0

         )

 )

dd.jpg

It gave me a list of marked 14 days as on the picture.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After that I have created some Measure with filter for the Last 7 days:

MeasurePrevious7Days:=CALCULATE(DISTINCTCOUNT(ReportData[UserID]), filter(dimdate, dateadd(dimdate[Date],1,day)<=TODAY() && dateadd(dimdate[Date],8,day)>TODAY()))

 

and the same for Previous 7 days:

MeasurePrevious7Days:=CALCULATE(DISTINCTCOUNT(ReportData[UserID]), filter(dimdate, dateadd(dimdate[Date], 8, day) <=TODAY() && dateadd(dimdate[Date],15,day)>TODAY()))

 

It allow me to build PBI-report which shows 2 lines as comparison of last and previous 7 days, and I do not need to change Date filters on report or in the DAX formulas, they are in default calculated for periods of time, that I need. And these Dates change itself day by day.

 

Thanks for reading. 

 

 

 

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 323 members 3,110 guests
Please welcome our newest community members: