Forum Discussion

jostnachs's avatar
jostnachs
Helper IV
1 year ago
Solved

Custom date filter

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 t...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

v-menakakota's avatar
v-menakakota
Community Support
1 year ago

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's avatar
    v-menakakota
    Community Support
    1 year ago

    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's avatar
      v-menakakota
      Community Support
      1 year ago

      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's avatar
        v-menakakota
        Community Support
        1 year ago

        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.