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.
To Date = max(ToCal[Date])
write the a measure that reacts to the slicers. Something like this.
total sales selected period =
Sales[date] >= [From Date] &&
Sales[Date] <= [To Date]
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(),
if(dateadd(dimdate[Date],8,day)<=TODAY() && dateadd(dimdate[Date],15,day)>TODAY(),
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.
Check out the News & Announcements to learn more.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.