Share via

Power BI Dynamic Date Filtering

  • Article

One question I get from time to time is how to filter to the last week's worth of data automatically in Power BI.  You might want the last 7 days, this week, this month, etc. 

You can make it fully dynamic by combining the technique Matt Masson describes here to create a date table and add to it some Power Pivot DAX functions to classify each date into the date range you'd like. 

Download a sample here

OneDrive personal doesn't load the Power View sheet so download and open the example in Excel on your desktop.

 

EDIT 7.27.2015  - I was asked for the formulas since some folks don't have Power View.  These should work in Power BI Desktop, but I've not tested them there.  Here you go:

Power Query query:

//let
//    CreateDateTable = (StartDate, EndDate) =>
let

    StartDate=#date(2015,1,1),
    EndDate=#date(Date.Year(DateTime.LocalNow()),12,31),

    //Create lists of month and day names for use later on
    MonthList = {"January", "February", "March", "April", "May", "June"

                 , "July", "August", "September", "October", "November", "December"},

    DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},

    //Find the number of days between the end date and the start date

    NumberOfDates = Duration.Days(EndDate-StartDate),

    //Generate a continuous list of dates from the start date to the end date

    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

    //Turn this list into a table

    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}

                     , null, ExtraValues.Error),

    //Cast the single column in the table to type date

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),

    //Add custom columns for day of month, month number, year

    DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),

    MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),

    Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),

    DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),

    //Since Power Query doesn't have functions to return day or month names,

    //use the lists created earlier for this

    MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),

    DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),

    WeekEnding = Table.AddColumn(DayName, "Week Ending", each Date.EndOfWeek([Date])),
    #"Changed Type" = Table.TransformColumnTypes(WeekEnding ,{{"DayOfMonth", Int64.Type}, {"MonthNumberOfYear", Int64.Type}, {"Year", Int64.Type}, {"DayOfWeekNumber", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthYear", each Text.Range([MonthName], 0, 3) & "-" & Number.ToText([Year])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MonthYearNumber", each [Year] * 1000 + [MonthNumberOfYear]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthYearNumber", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Week Ending", "Copy of Week Ending"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Week Ending", "WeekEndingDate"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"WeekEndingDate", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Week Ending", "WeekEnding"}})
in
    #"Renamed Columns1"
//in
//    CreateDateTable

 

DAX Measures
Today:=DATE(year(now()),MONTH(NOW()), DAY(NOW()))

DAX Calculated Columns
IsInCurrentWeek
=if([isCurrentYear] && WEEKNUM(NOW())=[WeekOfYearNumber],1,0)

IsInLastWeek
=if([isCurrentYear] && (WEEKNUM(NOW())-1)=[WeekOfYearNumber],1,0)

IsInCurrentYer
=if(YEAR(NOW())= [Year],1,0)

WeekOfYearNumber
=WEEKNUM([Date])

IsLast30Days
=if( AND( [Date]  >= [Today]  - 30 , [Date] <= [Today] ),1,0)

 HTH,

-Lukasz

Comments

  • Anonymous
    June 23, 2016
    This is such a great idea. Microsoft really need to offer this type of functionality in the future. Till then, we'll be using this in our Power BI reports.
  • Anonymous
    June 29, 2016
    THANKS for including this. This IS a super helpful post!