Help with DAX query active clients by branch and month

[marcus_giddings]

I’m having trouble with figuring out what isnned to get the solution that i need.

I have three tables.

1. A table that contains details on a client such as start date , end date and the branch they belong to.
2. A table of branches, contains id and name.
3. A date table, contains date and month year number.
4. A table for visits, contains id, client id stat and end

• The clients table has a UID per client and the end_date can be black if the client hasnt leftour service yet

Clients

id	first_name	last_name	start_date	end_date	branch
1	Deborah	Hutton	01/01/2021	01/02/2021	a
2	Darrin	Whiterod	02/01/2021	{{Null}}	b
3	Madelene	Redemile	03/01/2021	03/02/2021	c
4	Roda	Merck	04/01/2021	{{Null}}	d
5	Bastien	Austwick	05/01/2021	05/03/2021	a
6	Gisele	Shankle	06/01/2021	06/03/2021	b
7	Sonnie	Jiroutka	07/01/2021	{{Null}}	c
8	Alta	Vedenyapin	08/01/2021	28/01/2021	d
9	Sarah	Habens	09/01/2021	09/02/2021	a
10	Torrey	Cragg	10/01/2021	{{Null}}	a

Branch

id	branch_name
1	a
2	b
3	c
4	d

Date Table

DateYearMonthnumber

01/01/2021 00:00	01/01/2021
02/01/2021 00:00	01/01/2021
03/01/2021 00:00	01/01/2021
04/01/2021 00:00	01/01/2021
05/01/2021 00:00	01/01/2021
06/01/2021 00:00	01/01/2021
07/01/2021 00:00	01/01/2021
08/01/2021 00:00	01/01/2021
09/01/2021 00:00	01/01/2021

Visits

client_id	visit_start	visit_end
1	01/01/2021 10:30	01/01/2021 11:30
2	02/01/2021 08:00	02/01/2021 10:30
3	03/01/2021 06:00	03/01/2021 07:30
4	04/01/2021 10:00	04/01/2021 10:30
5	05/01/2021 01:00	05/01/2021 10:30
6	06/01/2021 10:15	06/01/2021 10:30
7	07/01/2021 23:00	08/01/2021 10:30
8	08/01/2021 05:00	08/01/2021 09:30
9	09/01/2021 02:00	09/01/2021 03:30
10	10/01/2021 07:00	11/01/2021 08:30
1	01/02/2021 10:00	01/02/2021 10:30
2	01/02/2021 10:00	01/02/2021 10:30
3	01/02/2021 10:00	01/02/2021 10:30
4	01/02/2021 10:00	01/02/2021 10:30
5	01/02/2021 10:00	01/02/2021 10:30
6	01/02/2021 10:00	01/02/2021 10:30
7	01/02/2021 10:00	01/02/2021 10:30
7	01/02/2021 14:00	01/02/2021 14:30
9	01/02/2021 10:00	01/02/2021 10:30
10	01/02/2021 10:00	01/02/2021 10:30

clients to branch is a one to many

visit to clients is one to many

both clients and visits are linked to dates one to many 

I need to produce visualization that show the average number of hours for active clients at a branch for each given month, i know what the table that i would want to produce would/should look like but unshure how to reproduce it in power bi .

wanted table

	Jan-21	Feb-21
a	9.325	0.5
b	1.225	0.5
c	6.5	0.65
d	2.5	0.3

[amitchandak · Accepted solution]

@marcus_giddings , I think you need measure very similar to the current employee in this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...