Innofactor's deep-dive blogs provide in-depth insights into various Microsoft technologies, including Azure, Dynamics, Power BI, and Data Platforms. Written by our talented professionals, these articles are designed to help you discover new tips and tricks.
In this blog, we discuss an implementation required by one of our customers.
As of October 2024, Power BI does not offer an easy mechanism for filtering data based on a date that could be in either the past or future. In the report I created, we needed to display all relevant data up to a specific date, which could be a few days before or after today.
In the available filters, you can choose either a past date, the current date, or a future date:
If you need the flexibility to select either a past or future date, the easiest approach is to enable both past and future filters. When both are enabled, report users can choose either, with the default selection being today. However, this method can be confusing for users and requires additional documentation or training to ensure proper usage.
Target outcome
To make date selection more user-friendly, I created a slicer (using a new slicer type) with the following configuration:
We need an anchor value to serve as the default date. This anchor can be yesterday, today, or tomorrow, for example. Each time the user opens the report, they are presented with this anchor date as the default. Without this anchor, you might set a fixed date, but after seven days, it will no longer be a suitable solution, and no date will be selected.
M / Power Query
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",),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",),
#"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 allowable dates for the selector. To make the code cleaner, you can set these as variables. Note that I’ve replaced today's date with "Today" as text. Depending on your use case, you may not need all of these lines in your solution.
The result should yield two columns: one with a date and one with a text value:
If there are too many dates to fit within the slicer, it will adjust accordingly.
First, you can specify the number of rows and columns to display in the slicer under Visual → Layout → Max rows shown and Columns shown:
The second option is to allow values to overflow, which will create a scrollbar on the right side of the slicer:
Model
Now, we have two date tables. We want the report to display data that is relevant up to the selected date, not necessarily data created on that date. Because of this, I did not create a direct relationship between the date/calendar tables, but you may still choose to do so:
I defined both tables as Date type, though this is not mandatory.
UI
You can use either the old or new slicer for the text value of DueDateSelector:
I chose the following settings for the UI:
DAX
In this report, we are only concerned with the date as a maximum value. I applied the date filter directly to the fact data, but you may opt to do this through the Date/Calendar table to ensure consistency with other potential 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()
)
If you found this post helpful, be sure to check out our previous deep-dive blog on Power BI insights and tips.
