Forum Discussion

gjadal's avatar
gjadal
Microsoft Employee
10 years ago

Date filter

What is the best practice to add From date and To Date filters on my report and default them to a week?
Need Help
greggyb's avatar
greggyb
Resident Rockstar
10 years ago

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's avatar
AlexSavchenko
Frequent Visitor
9 years ago

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

         )

 )

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.