Data Calculations across multiple columns

[Anonymous]

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

[sturlaws]

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

[Anonymous]

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.

[sturlaws]

could you be a bit more specific?

[Anonymous]

Hi Sturla

The actual data comprises of thousands of records. When I have filtered the data in the excel file, for example for May for program X, the number of active or open customers is lower than the power bi number by nearly 50 customers. I am not sure what the next steps are. Am I able to get row level records from power bi for a particular month with the details so I can reconcile to the excel file to see where the difference is? This may shed more light.

[sturlaws]

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.

[Anonymous]

Hi Sturla

The date table ranges from 2015 to 2030 yo encompass all data. Would this be the issue? If so any ideas on fixes?

Thanks

[sturlaws]

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.

[Anonymous · Accepted solution]

Hi Sturla

Thanks for the information but I am not sure where this applies?

[sturlaws]

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