Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Searching data in common from two list

Hi everyone,
I have a question.

I have this fact Table that is about memberships from a gym.

CustomerStart DateEnd Date
101/03/202328/02/2024
201/04/202331/03/2024
301/05/202330/04/2024
415/06/202330/06/2023

 

So, what I need to do is (the customer will choose from a slicer 1 complete month/year, eg: March 2023.) to count how many customers (with distinctcount) are active in the month that I choose.


But not to check only the month, because the membership can be only 2 weeks. I need to check every date that the customer select (from 1/3 to 31/3) and check if one of those 31 dates fit between Start and End Date.

 

So if the customer choose March/23, the answer is only 1, if the customer choose April/23, the answer is 2, in May/23 is 3 and so on.

 

Hope that understood what I need. 

 

Thank you.

Need Help

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Ahmedx , thanks for the reply.

       

      I don't understand how this is going to help. What I see in those topics, is to count how many dates are in both list.

       

      What I need is something like this:

      CALCULATE ( [Distinct Count Customers]
              , [one of the dates in the List of the dates selected from dimDates]
                in
               [List of Dates between Start Date and End_Date]
      )

       

      [one of the dates in the List of the dates selected from dimDates]: This list is going to be all the dates between 1st and 31st of March 2023

       

      [List of Dates between Start Date and End_Date]: This list is going to be for each line from the fact that I wrote above.

       

  • MarceDF's avatar
    MarceDF
    Frequent Visitor
    3 years ago

    I sign in with my personal user.

    Ahmedx or maybe something like this.

     

    CALCULATE ( [Distinct Count Customers]
            , NOT ( ISBLANK ( [INTERSECTION of 2 date lists] ) )
    )

     

    I don't know.

  • MarceDF's avatar
    MarceDF
    Frequent Visitor
    3 years ago

    Hi Ahmedx , look what I receive.

     



    This is the measure with the changes:

    Active Members History =


    VAR _Date =
        VALUES ( dimDates[Date] )
    VAR _tbl1 =
        SELECTCOLUMNS (
            GENERATE (
                'factCustomerHistoryHistory',
                DATESBETWEEN ( 'dimDates'[Date], 'factCustomerHistoryHistory'[Start_Date], 'factCustomerHistoryHistory'[End_Date] )
            ),
            "@Customer", [Customer ID],
            "@Date", [Date]
        )
    VAR _tbl2 =
        GENERATE ( VALUES ( 'factCustomerHistoryHistory'[Customer] ), _Date )
    RETURN
        CALCULATE ( DISTINCTCOUNT ( 'factCustomerHistoryHistory'[Customer] ), INTERSECT ( _tbl2, _tbl1 ) )


    What do you think it is?

    • Ahmedx's avatar
      Ahmedx
      Icon for Super User rankSuper User
      3 years ago

      it says that it is not possible to align text with a number. I don't know the reasons for this, you could not give a link to your file to see

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I was using different fields for the customer... thank you Ahmed for the solution!