Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I am new to Power Bi and have got myself totally stuck on how to calculate the below.
I have a client management database which provides me with a table that contains information as per the below table. What I need to produce is visulisations which I can sort by program and gender by data active without duplicating the client for the same program.
To provide some examples:
Individual_name "A" is in both both programs X and Y and will need to be counted in both programs. For program X they should only be counted once for the period Nov 18 - June 19 as the 2 rows at row 2 and 3 encompass the same date ranges, although row 3 starts earlier. For the same individual in program Y they need to be counted from March 19 to May 19. In this instance the rows are different date ranges.
ind_id | individual_name | individual_gender | program_name | program_start_date | program_end_date |
2 | A | Male - Child | X | 1/03/2019 | 30/06/2019 |
2 | A | Male - Child | X | 29/11/2018 | 30/06/2019 |
2 | A | Male - Child | Y | 7/03/2018 | 11/04/2019 |
2 | A | Male - Child | Y | 15/05/2019 | 16/05/2019 |
8 | B | Female - Adult | X | 18/01/2019 | 1/03/2019 |
9 | C | Female - Adult | X | 5/01/2019 | 13/01/2019 |
10 | D | Female - Adult | X | 9/01/2019 | 6/04/2019 |
11 | E | Male - Adult | X | 5/01/2019 | 21/02/2019 |
12 | F | Female - Adult | X | 1/01/2019 | 29/03/2019 |
12 | F | Female - Adult | X | 18/01/2019 | 29/03/2019 |
13 | G | Female - Adult | X | 11/01/2019 | 7/03/2019 |
14 | H | Female - Adult | X | 4/01/2019 | |
15 | I | Female - Adult | X | 5/01/2019 | 10/03/2019 |
17 | J | Male - Adult | X | 10/01/2019 | 24/07/2019 |
17 | J | Male - Adult | Y | 25/01/2019 | 10/08/2019 |
18 | K | Female - Adult | X | 18/01/2019 | 18/07/2019 |
18 | K | Female - Adult | X | 15/02/2019 | 17/04/2019 |
19 | L | Female - Adult | X | 5/01/2019 | 5/06/2019 |
19 | L | Female - Adult | X | 10/02/2019 | 31/05/2019 |
20 | M | Female - Adult | X | 18/01/2019 | |
20 | M | Female - Adult | X | 11/05/2019 | 27/06/2019 |
21 | N | Female - Adult | Y | 5/02/2019 | |
21 | N | Female - Adult | Y | 14/02/2019 | |
21 | N | Female - Adult | Y | 14/02/2019 |
Individual N should only be counted once under program Y from Feb 19 ongoing, as there is no end date.
What I would anticipate is the end result to be something like:
Output | |||||||||
Program X | |||||||||
Client | Jan | Feb | March | April | May | June | July | Aug | Sep |
A | 1 | 1 | 1 | 1 | 1 | 1 | |||
B | 1 | 1 | 1 | ||||||
C | 1 | ||||||||
D | 1 | 1 | 1 | 1 | |||||
E | 1 | 1 | |||||||
F | 1 | 1 | 1 | ||||||
G | 1 | 1 | 1 | ||||||
H | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
I | 1 | 1 | 1 | ||||||
J | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
K | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
L | 1 | 1 | 1 | 1 | 1 | 1 | |||
M | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
N | |||||||||
Total | 13 | 12 | 11 | 7 | 6 | 6 | 4 | 2 | 2 |
Program Y | |||||||||
Client | Jan | Feb | March | April | May | June | July | Aug | Sep |
A | 1 | 1 | 1 | ||||||
J | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |
N | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |
Total | 1 | 2 | 3 | 3 | 3 | 2 | 2 | 2 | 1 |
Any help would be appreciated.
Thanks
Kate
Solved! Go to Solution.
Hi,
this really is a well formulated question, with data and expected outcome. Well done. There is one tiny flaw for your expected outcome for the Program=Y. Indivdual_name=A has program_start_date=2018-03-07 for Program=Y, so it should be counted for january and february as well.
First create a date table with the CALENDAR-function, e.g.
Dates = CALENDAR(DATE(2019;1;1);DATE(2019;12;31))
Then you can create this measure:
Measure = COUNTROWS ( GENERATE ( VALUES ( 'Table'[individual_name] ); CALCULATETABLE ( VALUES ( Dates[StartOfMonth] ); FILTER ( Dates; CALCULATE ( MIN ( 'Table'[program_start_date] ) ) <= Dates[Date] && IF ( CALCULATE ( COUNTROWS ( FILTER ( 'Table'; 'Table'[individual_name] = SELECTEDVALUE ( 'Table'[individual_name] ) && ISBLANK ( 'Table'[program_end_date] ) ) ) ) >= 1; TODAY () >= Dates[Date]; CALCULATE ( MAX ( 'Table'[program_end_date] ) ) >= Dates[Date] ) ) ) ) )
This code can be simplified a bit if you generate a calculated column where program_end_date=TODAY() when program_end_date is blank.
cheers,
Sturla
Thanks Sturla
I have created the measure and have created the visulisation and it looks great, but I am unable to get it to reconcile to the raw data. Power Bi appears to be overstating the numbers.
could you be a bit more specific?
There could be many reasons for this. You can create a page in pbi desktop like this
and then compare user for user with what you get in excel.
On thing that comes to mind, have you created a date table that spans more than 1 year? In that case using just month name may cause some trouble.
the number of years is not an issue, but using filters like 'january' can cause "errors", because that would be january every year. So instead of using
Month name = FORMAT(Dates[StartOfMonth];"MMM")
use
Month name = FORMAT(Dates[StartOfMonth];"MMM-yy")
Or just use Dates[StartOfMonth] directly on axis. But you might already have done that.
could you provide a screen shot of your report? You send it as a message if you are not comfortable with posting it in the forum
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |