I need active numbers per month and a filter included please

[RichOB]

Hi, I need to get the number of active clients per month (from table 1) and have the ability to filter the numbers by table 2.  Table 1 has the end date to get the active number per month, and Table 2 has the data I want to filter by

Table 1

Name	Created_Date	End_Date	ID
Dave	01/04/2024	01/12/2024	1
Mark	02/05/2024	30/07/2024	2
Jodie	03/06/2024	12/08/2024	3
Mary	05/06/2024	01/09/2024	4
Gavin	07/06/2024	11/08/2024	5
Holly	01/08/2024	01/10/2024	6
Abby	01/09/2024	01/20/2024	7
Bob	01/10/2024	01/12/2024	8
Bill	01/11/2024	31/12/2024	9
Jill	01/05/2024	01/11/2024	10

Table2

Name	Created_Date	ID	Location	Eye_Colour
Dave	01/04/2024	1	Manchester	Brown
Mark	02/05/2024	2	Manchester	Blue
Jodie	03/06/2024	3	Manchester	Brown
Mary	05/06/2024	4	Manchester	Blue
Gavin	07/06/2024	5	Edinburgh	Brown
Holly	01/08/2024	6	Edinburgh	Blue
Abby	01/09/2024	7	Manchester	Green
Bob	01/10/2024	8	Edinburgh	Green
Bill	01/11/2024	9	Edinburgh	Brown
Jill	01/05/2024	10	Manchester	Brown

For example, the full active clients per month are:
April 1

May 2

June 6

July 6

August 6

Active clients in June

Brown Eyes 4

Blue eyes 2

Green 0

Thanks!

[lbendlin · Accepted solution]

Table = 
var t1 = SELECTCOLUMNS('Table 1',"Name",[Name],"Created_Date",[Created_Date],"End_Date",[End_Date],"ID",[ID] & "")
var t2 = SELECTCOLUMNS('Table 2',"Location",[Location],"Eye Colour",[Eye_Colour],"ID",[ID] & "")
return NATURALINNERJOIN(t1,t2)

[HarishKM]

@RichOB Hey,
Kindly refer below blog for your reference as this person has similer problem- Solved: Calculate Number of Active Clients by Month - Microsoft Fabric Community

HR Analytics - Active Employee, Hire and Terminati... - Microsoft Fabric Community

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-kpoloju-msft]

Hi @RichOB,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @Ashish_Mathur @lbendlin, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Dax Measure for Creating Active Clients in the Table :

Active Clients =

CALCULATE(

    DISTINCTCOUNT('Table'[ID]),

    FILTER(

        'Table',

        'Table'[Created_Date] <= MAX(DateTable[Date]) &&

        'Table'[End_Date] >= MIN(DateTable[Date])

    )

)

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @RichOB,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @RichOB,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 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.

[v-kpoloju-msft]

Hi @RichOB,

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.

[Ashish_Mathur · Accepted solution]

Hi,

Assuming there will never be repetitions in the ID column of Table2, create a relationship (Many to One and Single) from the ID column of Table1 to the ID column of Table2.  Then follow the technique shown in the attached file.

[lbendlin]

What's the reason for having two tables? This can all go into a single table.  

Then add a calendar table and create a measure that computes if a particular user was active in the specified date

range.

Note:  "01/20/2024"  is not a valid en-GB date,  did you mean "20/01/2024"  ?

[RichOB]

Hi @lbendlin in my real-life scenario it's in two tables from 2 systems. What is the easiest way to combine, please? Ah yes it should say 20/01/2024.

Thanks

[lbendlin · Accepted solution]

Table = 
var t1 = SELECTCOLUMNS('Table 1',"Name",[Name],"Created_Date",[Created_Date],"End_Date",[End_Date],"ID",[ID] & "")
var t2 = SELECTCOLUMNS('Table 2',"Location",[Location],"Eye Colour",[Eye_Colour],"ID",[ID] & "")
return NATURALINNERJOIN(t1,t2)