Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
marcus_giddings
Frequent Visitor

Help with DAX query active clients by branch and month

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

idfirst_namelast_namestart_dateend_datebranch
1DeborahHutton01/01/202101/02/2021a
2DarrinWhiterod02/01/2021{{Null}}b
3MadeleneRedemile03/01/202103/02/2021c
4RodaMerck04/01/2021{{Null}}d
5BastienAustwick05/01/202105/03/2021a
6GiseleShankle06/01/202106/03/2021b
7SonnieJiroutka07/01/2021{{Null}}c
8AltaVedenyapin08/01/202128/01/2021d
9SarahHabens09/01/202109/02/2021a
10TorreyCragg10/01/2021{{Null}}a

 

Branch

idbranch_name
1a
2b
3c
4d

 

Date Table

DateYearMonthnumber

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

 

Visits

client_idvisit_startvisit_end
101/01/2021 10:3001/01/2021 11:30
202/01/2021 08:0002/01/2021 10:30
303/01/2021 06:0003/01/2021 07:30
404/01/2021 10:0004/01/2021 10:30
505/01/2021 01:0005/01/2021 10:30
606/01/2021 10:1506/01/2021 10:30
707/01/2021 23:0008/01/2021 10:30
808/01/2021 05:0008/01/2021 09:30
909/01/2021 02:0009/01/2021 03:30
1010/01/2021 07:0011/01/2021 08:30
101/02/2021 10:0001/02/2021 10:30
201/02/2021 10:0001/02/2021 10:30
301/02/2021 10:0001/02/2021 10:30
401/02/2021 10:0001/02/2021 10:30
501/02/2021 10:0001/02/2021 10:30
601/02/2021 10:0001/02/2021 10:30
701/02/2021 10:0001/02/2021 10:30
701/02/2021 14:0001/02/2021 14:30
901/02/2021 10:0001/02/2021 10:30
1001/02/2021 10:0001/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-21Feb-21
a9.3250.5
b1.2250.5
c6.50.65
d2.50.3

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User
1 REPLY 1
amitchandak
Super User
Super User

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors