Searching data in common from two list

[MarceloDF]

Hi everyone,
I have a question.

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

Customer	Start Date	End Date
1	01/03/2023	28/02/2024
2	01/04/2023	31/03/2024
3	01/05/2023	30/04/2024
4	15/06/2023	30/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.

[MarceloDF]

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

[MarceDF]

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]

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

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhzRl8052qJ9QGeVp?e=Vhf0Px

[MarceDF]

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.

[Ahmedx]

look at this, the principle of the solution is one

https://community.powerbi.com/t5/Desktop/Managing-absences-per-month/m-p/3165816#M1068662

or

https://community.powerbi.com/t5/Desktop/How-to-measure-days-between-dates-when-beginning-and-ending...

[MarceloDF]

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.