Filtering Future and Past Dates in Power BI

[TimoRiikonen]

Filtering Future and Past Dates

As of 2024-08 Power BI doesn’t offer easy mechanism to filter data based on a date that could be in past or future.

In the case of the report I did, we wished to show all the relevant data until a date, that can be a few days before or after today.

In filters you can choose either a past date, current date or future date:

If you wish to choose sometimes a past date and sometimes a future date, then the easiest option is to enable both past and future filters. When you have done so, report users can choose either one of them resulting with the default of today. But this is a difficult way for users, and it requires documentation and/or training for them to learn to use it.

Target result

To make date selection easier, instead I created a Slicer (of a new Slicer type) with following result:

We need an anchor in there for the default value. This anchor could be for example yesterday, today or tomorrow. So, every time user opens the report he gets the anchor value as default. Without the anchor, you might choose a fixed date but after seven days it won’t be an acceptable solution anymore and thus nothing will be chosen.

M / PowerQuery

DueDateSelector =

let
    DateList = {Number.From(DateTime.From( Date.AddDays( Date.From( DateTime.LocalNow() ), -6) ))..Number.From(Date.AddDays( Date.From( DateTime.LocalNow() ), 7) )},
    #"Converted to table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Renamed Columns", "Selected due date", each Text.BeforeDelimiter(Text.From([Date], "fi-FI"), ".", 1) & ".", type text),
    Today = Table.ReplaceValue(#"Inserted Text Before Delimiter", each [Selected due date], each if Date.From([Date]) = Date.From( DateTime.FixedLocalNow() + #duration(0, 6, 0, 0)) then "Today" else [Selected due date], Replacer.ReplaceText, {"Selected due date"})
in
    Today

As you can see, you can define the minimum and maximum acceptable dates for the selector.

To beautify the code, you could set them as a variable as well. Note that I have replace today’s date as “Today” as text. You might not need all these lines in your solution.

In the result, you should have two columns: one with a date and another with text type:

If you have too many dates to fit in the slicer, it will manage with that.

First, you can define in Visual à Layout à Max rows shown, and Columns shown how many dates can be inserted in the slicer and the slicer will comply with that the best it can:

Second option is to let values overflow creating a lift to the right side of the slicer:

Model

So now we have two date tables. We don’t want the report to show data that had been created on the chosen date, but instead data, that is of interest, and is at latest the chosen date. Therefore, I could not make a connection between the date/calendar tables, but you may wish to do that still:

I have defined both tables as a Date type, but this definition is not mandatory.

UI

Use old or new slicer for text value of DueDateSelector:

I chose these settings for the UI:

DAX

In this report we are interested of the date only as a maximum value. I have made the date definition directly on the fact data, but you might opt to do it through the Date/Calendar table to make the code more consistent with possible other date filters.

Remaining Amount LCY / day =

VAR Result = 
SUMX(
    FILTER(
        'VendorLedgerEntries',
        VendorLedgerEntries[Document_Type] IN {"Invoice", "Refund"} && VendorLedgerEntries[Open] = TRUE() && 'VendorLedgerEntries'[Due_Date] <= MAX('DueDateSelector'[Date]) 
    ),
    VendorLedgerEntries[Remaining_Amt_LCY] * -1
)
RETURN
If(Result = 0, BLANK(), Result)

Remaining Amount LCY (Invoice/Refund) =

VAR Result = 
SUMX(
    FILTER(
        'VendorLedgerEntries',
        VendorLedgerEntries[Document_Type] IN {"Invoice", "Refund"} && VendorLedgerEntries[Open] = TRUE() && 'VendorLedgerEntries'[Due_Date] <= MAX('DueDateSelector'[Date])
    ),
    VendorLedgerEntries[Remaining_Amt_LCY] * -1
)
RETURN
If(Result = 0, BLANK(), Result)

Due Date =

VAR MaxDate = MAX('DueDateSelector'[Date])
VAR _EntryNo = MAX(VendorLedgerEntries[Entry_No])
VAR _CompanyNameInternal = MAX(VendorLedgerEntries[CompanyNameInternal])
VAR _TransactionNo = MAX(VendorLedgerEntries[Transaction_No])
VAR _DueDate =  
MINX(
        FILTER(
            'VendorLedgerEntries', 
            'VendorLedgerEntries'[Entry_No] = _EntryNo && VendorLedgerEntries[CompanyNameInternal] = _CompanyNameInternal && VendorLedgerEntries[Transaction_No] = _TransactionNo && VendorLedgerEntries[Due_Date] <= MaxDate
        ),
        VendorLedgerEntries[Due_Date], FALSE
    )
RETURN
IF(
    ISINSCOPE(VendorLedgerEntries[Entry_No]) && COALESCE(_DueDate, MaxDate + 1) <= MaxDate, 
    _DueDate, 
    BLANK()
)