Forum Discussion
Custom date filter
Hi jostnachs
Use a disconnected dates table and create this measure:
Client by Time Period = VAR StartDate = MIN ( Dates[Date] ) VAR EndDate = MAX ( Dates[Date] ) RETURN COUNTROWS ( FILTER ( Client, Client[EffectiveDate] <= EndDate && Client[ExpirationDate] >= StartDate ) )This will return more than 1 only in the dates when the expiraiton and effective dates overlap
Please see the attached pbix for details.
Note: moving forward, please use a sample that we can easily copy paste as table to excel and not an image.
Hi jostnachs ,
To find the active clients based on the selected date in Power BI, you can create a calculated column or a measure. Here's how you can achieve this:
Steps:
Load Your Tables:
Import the Client Table and Fact Table into Power BI.Define a Date Slicer:
Add a date slicer to your report so users can select a specific date (e.g., 18/12/2024).Create an "Active Today" Measure:
Use the following DAX measure to determine if a client is active on the selected date:Active Clients =
VAR SelectedDate = MAX('Calendar'[Date]) -- Use a Date table or slicer for the selected date
RETURN
CALCULATE(
DISTINCTCOUNT(ClientTable[ClientName]),
FILTER(
FactTable,
FactTable[EffectiveDate] <= SelectedDate
&& FactTable[ExpirationDate] >= SelectedDate
)
)- #Replace ClientTable and FactTable with the actual names of your tables.
Use the Measure in a Visual:
Add the Client Name from your Client Table into a table or matrix visual, and apply this measure as a filter:Set the measure to "is not blank" or > 0 to filter only active clients.
Add a Date Slicer:
Add a slicer for the date column (e.g., from a Calendar table) to allow users to select the "active today" date.
OR
Alternative: Create a Calculated Column
If you prefer to filter directly in the Fact Table, you can create a calculated column in the Fact Table:
IsActive =
VAR SelectedDate = TODAY() -- Or a custom date from a slicer
RETURN
IF(
FactTable[EffectiveDate] <= SelectedDate
&& FactTable[ExpirationDate] >= SelectedDate,
1,
0
)
You can then filter the table where IsActive = 1.
Expected Outcome:
When the selected date is 18/12/2024, the result will include:
- Client B (Effective Date: 15/10/2023 – Expiration Date: 15/10/2024)
- Client C (Effective Date: 15/10/2024 – Expiration Date: 15/10/2025)
Let me know if you need help setting this up!
If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.