Power BI Date Range Filter

Question

I have a table with accounts and when they were active that looks like this:

user	active_start	active_end
A	1/1/2023	12/31/2023
B	2/5/2023	10/31/2023
C	5/11/2023	6/28/2023

I want to create a Power BI dashboard where a user can see how many users were active at some point during a date range of their choosing. For example, if they need to know how many users were active from 1/1/2023 - 3/1/2023 it would show 2. If they wanted to select 4/1/2023 - 12/31/2023 it would show 3.

How can I accomplish this? Is this even possible?

Answer 1

If you want to have just the one slicer, and/or allow the user to select a month etc... then you will need a Date table for your model that you will use for your slicer. An example Date table could be created with these steps:

1. Create a Calculated Table (via New Table under the Modeling tab in the ribbon) with the following:

DateTable = 
  ADDCOLUMNS(
    CALENDARAUTO(),
    "Month", FORMAT([Date], "MMM"),
    "MonthNum", MONTH([Date]),
    "Year", YEAR([Date]),
    "Year-Month", FORMAT([Date], "yyyy-MM")
  )

2. Once created, click on the Month column in the DateTable, then Sort by column in the ribbon, and select MonthNum. This will ensure Jan, Feb etc... will be ordered correctly.
3. Right-click on DateTable and select Mark as date table and in the pop-up select Date as the Date column.

That's your date table created. Next, ensure no relationship exists between the date table and your table. Then create a Measure with:

Active User count = 
  CALCULATE(
    DISTINCTCOUNTNOBLANK('YourTable'[user]),
    'YourTable'[active_start] <= MAX(DateTable[Date]) &&
    'YourTable'[active_end] >= MIN(DateTable[Date])
  )

For your slicer, use the DateTable[date] and set it to Between.