cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KateC
Frequent Visitor

Data Calculations across multiple columns

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_idindividual_nameindividual_genderprogram_nameprogram_start_dateprogram_end_date
2AMale - ChildX1/03/201930/06/2019
2AMale - ChildX29/11/201830/06/2019
2AMale - ChildY7/03/201811/04/2019
2AMale - ChildY15/05/201916/05/2019
8BFemale - AdultX18/01/20191/03/2019
9CFemale - AdultX5/01/201913/01/2019
10DFemale - AdultX9/01/20196/04/2019
11EMale - AdultX5/01/201921/02/2019
12FFemale - AdultX1/01/201929/03/2019
12FFemale - AdultX18/01/201929/03/2019
13GFemale - AdultX11/01/20197/03/2019
14HFemale - AdultX4/01/2019 
15IFemale - AdultX5/01/201910/03/2019
17JMale - AdultX10/01/201924/07/2019
17JMale - AdultY25/01/201910/08/2019
18KFemale - AdultX18/01/201918/07/2019
18KFemale - AdultX15/02/201917/04/2019
19LFemale - AdultX5/01/20195/06/2019
19LFemale - AdultX10/02/201931/05/2019
20MFemale - AdultX18/01/2019 
20MFemale - AdultX11/05/201927/06/2019
21NFemale - AdultY5/02/2019 
21NFemale - AdultY14/02/2019 
21NFemale - AdultY14/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        
ClientJanFebMarchAprilMayJuneJulyAugSep
A111111   
B111      
C1        
D1111     
E11       
F111      
G111      
H111111111
I111      
J1111111  
K1111111  
L111111   
M111111111
N         
Total131211766422
          
Program Y        
ClientJanFebMarchAprilMayJuneJulyAugSep
A  111    
J11111111 
N 11111111
Total123332221

Any help would be appreciated.

 

Thanks

Kate

1 ACCEPTED SOLUTION

Accepted Solutions
KateC
Frequent Visitor

Re: Data Calculations across multiple columns

Hi Sturla

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

View solution in original post

9 REPLIES 9
Super User III
Super User III

Re: Data Calculations across multiple columns

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

KateC
Frequent Visitor

Re: Data Calculations across multiple columns

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.

Super User III
Super User III

Re: Data Calculations across multiple columns

could you be a bit more specific?

KateC
Frequent Visitor

Re: Data Calculations across multiple columns

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.
Super User III
Super User III

Re: Data Calculations across multiple columns

There could be many reasons for this. You can create a page in pbi desktop like this
Data Calculations across multiple columns.PNG

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.

 

KateC
Frequent Visitor

Re: Data Calculations across multiple columns

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
Super User III
Super User III

Re: Data Calculations across multiple columns

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.

 

 

KateC
Frequent Visitor

Re: Data Calculations across multiple columns

Hi Sturla

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

View solution in original post

Super User III
Super User III

Re: Data Calculations across multiple columns

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors