Forum Discussion
Helper IDates in Power Bi - Next 4 weeks etc
Hi
In power BI i have a list of records with a date, using a slicer or time slicer i cant find a way of showing results for "next 4 weeks" or "next 2 weeks". I want it so that when the user opens the report he just sees records where the date field is in the next 4 weeks.
How to do this? i keep reading about measures etc but not really finding any step by step guides for complete dummies?
Dates do seem to be a problem in power BI!!!
You could follow these steps
1. Create a measure called CurrentYear = Year(Today()) . This is to find the year we are in.
2. Create a measure called CurrentWeekNumber = = Weeknum(Today(),2). This assumes the week begins on Monday.
3. Compute the StartDate for 4 weeks from CurrentWeekNumber
StartDate = Date([CurrentYear],1,-3) - Weekday(Date([CurrentYear],1,2))+([CurWeekNum]+4) * 7
4. Compute the EndDate of the 4 weeks from now,
EndDate = [StartDate]+6
5. Now you need to create a Date Table using the expression
DateTable = CALENDAR (ToDay(), [EndDate])
This will create a DateTable with Date column containing values from TOday upto end of 4 weeks from now.
This will create the column Date in the table automatically. Remember to set the format for this column in dd/mm/yyyy format or the date format you use.
6. Create a column called Weeknum in the Date Table using
WeekNum = Weeknum(DateTable[Date],2)
7. Now Create a Slicer showing the Date column from the Date Table.
8. You should now be able to the dates in the next 4 weeks including current date.
Hope this explains.
If you need further reply to this.
For explanation on the formuals used to compute the dates you may refer
If you find this working please accept as solution and mark as kudos.
Best
2 Replies
You could follow these steps
1. Create a measure called CurrentYear = Year(Today()) . This is to find the year we are in.
2. Create a measure called CurrentWeekNumber = = Weeknum(Today(),2). This assumes the week begins on Monday.
3. Compute the StartDate for 4 weeks from CurrentWeekNumber
StartDate = Date([CurrentYear],1,-3) - Weekday(Date([CurrentYear],1,2))+([CurWeekNum]+4) * 7
4. Compute the EndDate of the 4 weeks from now,
EndDate = [StartDate]+6
5. Now you need to create a Date Table using the expression
DateTable = CALENDAR (ToDay(), [EndDate])
This will create a DateTable with Date column containing values from TOday upto end of 4 weeks from now.
This will create the column Date in the table automatically. Remember to set the format for this column in dd/mm/yyyy format or the date format you use.
6. Create a column called Weeknum in the Date Table using
WeekNum = Weeknum(DateTable[Date],2)
7. Now Create a Slicer showing the Date column from the Date Table.
8. You should now be able to the dates in the next 4 weeks including current date.
Hope this explains.
If you need further reply to this.
For explanation on the formuals used to compute the dates you may refer
If you find this working please accept as solution and mark as kudos.
Best
- kcantor
Community Champion
You will need to use DAX Time Intelligence. This means you will also need a date table to build your calculations. Here is a good place to start on that:
http://www.daxpatterns.com/time-patterns/
Also, here is a new series of educational articles that will help with the DAX:
https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-7-1-intro-to-dax/
This one is specific to date calculation based slicers:
http://www.powerpivotpro.com/2011/04/ab-campaign-analysis-with-start-end-date-slicers/
And this one could be expanded to fit your request:
http://www.powerpivotpro.com/2013/03/guest-post-always-show-yesterday-today-or-tomorrows-data/
Now, that being said, post a sample of your data and we can see about specific DAX answers to your questions. People here are always happy to lend a hand in writing some DAX.
