cancel
Showing results for
Did you mean:
Highlighted
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.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Data Calculations across multiple columns

Hi Sturla

Thanks for the information but I am not sure where this applies?
9 REPLIES 9
New Contributor

## 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

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.

New Contributor

## Re: Data Calculations across multiple columns

could you be a bit more specific?

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.
New Contributor

## Re: Data Calculations across multiple columns

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.

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
New Contributor

## 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.

Frequent Visitor

## Re: Data Calculations across multiple columns

Hi Sturla

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

## 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

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)