Measure for counting periods in table 2 which match periods in table 1

[Joris_NL]

Hi all,

I'm new to DAX and struggling.

I have a LIST OF MONTHS. For each month we have to calculate the NUMBER of rows (clients) that have an ACTIVE and also LONG RELATIONSHIP (>732 days) at the moment of the given month (from the list).

TableListOfMonths

GivenMonthStart	GivenMonthEnd	NumberOfClients
1-1-23	31-1-23	?
1-2-23	28-2-23	?
1-3-23	31-3-23	?
1-4-23	30-4-23	?

TableInput

ClientNr	StartRelationship	EndRelationship	DurationRelationship
1111111	1-1-15	1-1-19	1462
2222222	1-1-22	1-1-25	1097
3333333	13-2-21	15-3-23	760

(client numbers are already distinct in this table, no doubles)

So, client 11111 isn't active in any of the given months. We don't count this one.

Client 22222 is active in all given months, but at that time, the duration of the relationship is less than 732 days. So it shouldn't count either.

Client 33333 is only active in the first three months, not the fourth. However, it's relationship is only 732 days halfway in the second month, so not long enough for the first month. It should count, but only towards the second and third row.

With this limited dataset, column NumberOfClients should result in:

0

1

1

0

Do you know how to DAX this? Thanks for the help already!!!

Querys are also fine, I can do those.

Regards Joris

[Joris_NL]

@v-yalanwu-msft I have an additional question though.

Now I need to show an average bandwith of all the counted clients active for >2years. Just to show which months have a very low or very high number of counted clients.

I guess I need two new values for that:

BandwithBottom50%OfAverage (i.e. 0,25with the data example of 0-1-1-0)

BandwithTop150%OfAverage (0,75 with the data example)

Then with those values, I simply place a new graph on top of the other bar graph, have it visually look the same and will work it out.

Unfortunately, simply making a new measure which calculates with our 'Count' measure didn't work for me. It gives an error that a column is expected, not a measure.

Any help would be very much appreciated!

[Joris_NL]

Wow it works!!!

Thank you!

[v-yalanwu-msft · Accepted solution]

Hi, @Joris_NL ;

You could create a measure as follow:

count = 
CALCULATE(COUNT('Table'[ClientNr]),
FILTER('Table',[StartRelationship]<MAX('Table2'[GivenMonthEnd])
                &&DATEDIFF([StartRelationship],MAX('Table2'[GivenMonthEnd]),DAY)>=732
                &&[EndRelationship]>=MAX('Table2'[GivenMonthStart])))+0

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.