Count of active items by date

[JohnM1985]

Hi,

I have a table of accounts, which includes the account number, status (A=Active, C=Closed), account opening date and closing date (blank if still open).

Both of the date columns have an inactive relationship to a calendar[Date] field - inactive due to a previous relationship which would introduce ambiguity between other tables.

 I am trying to create a simple visualisation which would result in showing the amount of active accounts at a given date, with an additional visualisation showing the amount opened and closed in the period - preferably monthly.

A simplified version of my table is shown below:

account_number		open_date		close_date		status
1		01/12/2021		01/01/2023		C
2		01/12/2021				A
3		01/03/2022				A
4		01/03/2022		01/03/2023		C
5		01/06/2022				A
6		01/12/2022				A
7		01/03/2023		01/03/2023		C
8		01/06/2023		01/09/2023		C
9		01/09/2023				A
10		01/12/2023				A

If displayed numerically I would expect the results to look similar to below:

Date		Running count of open accounts		Opened		Closed
01/12/2021		2		2		0
01/03/2022		4		2		0
01/06/2022		5		1		0
01/09/2022		5		0		0
01/12/2022		6		1		0
01/03/2023		4		1		3
01/06/2023		5		1		0
01/09/2023		5		1		1
01/12/2023		6		1		0

I've tried a variety of measures with no success, so would appreciate some help.

Thanks in advance!

[JohnM1985 · Accepted solution]

Hi @Abhilash_P , thanks so much for your quick response.

I had to make a couple of tweaks as the relationships had to stay inactive due to other connections, but all resolved now.  Thanks so much for your help!

[Abhilash_P]

Hi @JohnM1985 ,

You can create calendar table using below dax 

- Create Active relationship between calendar table date to open_date
- Create Incative relationship between calendar table date to close_date userrelationship dax to get count of closed accounts.

      

Create below measures

1. OPEN = CALCULATE(COUNT('Table'[open_date]),'Table'[status] = "A")

2.Closed=CALCULATE(COUNT('Table'[close_date]),USERELATIONSHIP('Table'[close_date],'Calendar'[Date]),'Table'[status] = "C")

3. Count of account numbers = COUNT('Table'[account_number])

4. Running count of open accounts =

VAR MaxD = MAX('Table'[open_date])

return

CALCULATE([Count of account numbers],'Calendar'[Date] <= MaxD,ALL('Calendar'[Date]))

below is your output.date in below  table should be selected from calendar table

Regards