Power BI Dynamic Date Filtering

Article
03/05/2015

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

Power BI Dynamic Date Filtering

Additional resources