Power BI Dynamic Date Filtering


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 (2)

  1. Curtis says:

    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.

  2. THANKS for including this. This IS a super helpful post!

Skip to main content