Dates in Power Bi - Next 4 weeks etc

[P0W3RB1]

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

[Anonymous · Accepted solution]

@P0W3RB1

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

https://www.ablebits.com/office-addins-blog/2015/04/29/excel-weeknum-function-convert-week-number-da...

If you find this working please accept as solution and mark as kudos.

Best

[kcantor]

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.