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

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
Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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..

 

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.

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.

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. 

 

 

 

 

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors