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 ,

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.