Forum Discussion

SGBaringa's avatar
SGBaringa
Frequent Visitor
2 years ago
Solved

Does value fall in specific month

Hello PBI community!

 

I have a task that I am struggling with and hope someone can help me out.

 

I have this Main table (for the sake of the example I have only included 2 clients):

ClientStart DateEnd Date

A

01/01/202301/03/2023

B

01/01/202301/06/2023

 

I also have a standard calendar table with dates.

 

What I would like to achieve, is to create a summary table which has all of the dates in the Calendar table as month and year as one column and another column that counts whether a client was active for the specific month.

 

For example, client A starts from Jan and ends in Mar so it would be counted for Jan, Feb. Client B starts from Jan and ends Jun, so it will be counted for Jan, Feb, Mar, Apr, May.

 

Example table below:

DateClient count
Jan2
Feb2
Mar1
Apr1
May1

 

I would really appreciate any help.