Custom date filter

[jostnachs]

Hi All... I have a requirement .

I have 2 tables. client table (client name) and fact table(effective date & Expiration date). I have to have a active today filter to find the active clients as of today. i.e if i select date as 18/12/2024 the result has to fetch me B & C from the below. 

[v-menakakota]

Hi @jostnachs 

Thank you for reaching out to us on the Microsoft Fabric Community Forum

• To find active clients based on Effective Date and Expiration Date for particular date(18/12/2024)
• Take two tables of name Client Table(ClientName) and Fact Table(ClientName,EffectiveDate and Expoiration Date).
• Give a relationship between two tables(one-to-many) where Client Table has the unique clients, and the Fact Table contains multiple records for each client.
• Create a Date Table using following DAX formula:

 DateTable = CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31))  

We can  adjust the date range as necessary.

Select Fact table create new measure

 ActiveStatus_StaticDate = VAR SelectedDate = DATE(2024, 12, 18)  -- Static date: 18/12/2024

RETURN

   IF

(   

    MAX('FactTable'[EffectiveDate]) <= SelectedDate &&         MAX('FactTable'[ExpirationDate]) >= SelectedDate,  

     "Active",   

    "Inactive" 

  )

• Take a slicer and drag Date field from DateTable
• Take Client Name field from Client table into row section and drag Active status measure to the values section.
• When you select 18/12/2024 in the slicer it will show whether it is active or inactive.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

Thank you

[v-menakakota]

Hi @jostnachs ,

I just wanted to kindly follow up to see if you had a chance to review the previous response provided by me. Please let me know if it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.

Thank you.

[v-menakakota]

Hi @jostnachs ,

Hi 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.


Thank you.

[v-menakakota]

Hi @jostnachs ,

I wanted to check if you had the opportunity to review the information provided. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[grazitti_sapna]

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:

1. Load Your Tables:
   Import the Client Table and Fact Table into Power BI.

2. Define a Date Slicer:
   Add a date slicer to your report so users can select a specific date (e.g., 18/12/2024).

3. 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.

4. 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.

5. 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.

[danextian · Accepted solution]

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.