Cumulative count between 2 dates column.

[Anonymous]

Hi All, 

I have a property data with columns start date and end date. I want to find the cumulative count of the property live based on the start date and end date based on the property status. 

Sample data:

Property ID	Property name	start date	end date	status
10001	xyz Motor Inn	20/03/2020		Live
10000	ABCy Villa	21/03/2020	20/04/2019	Terminated
10003	81 Hotel	24/03/2020		Live
10004	113 motel inn	24/03/2020		Live
100	ABC Lodge	20/04/2020		Live
10005	The Rope club	25/04/2020		Live
10006	Tiny Island	26/04/2020		Live
10007	Number41 serviced Apartments	27/04/2020		Live
10008	402 Morocco inn	28/04/2020		Live

1. When the end date is empty, the property is still live and can be counted. 

2. If the property is terminated, then the count will be applied only till the end date.

Expected output:

Property ID	Property name	start date	end date	status	Live_count
10001	xyz Motor Inn	20/03/2020		Live	1
10000	ABCy Villa	21/03/2020	20/04/2019	Terminated	2
10003	81 Hotel	24/03/2020		Live	4
10004	113 motel inn	24/03/2020		Live	4
100	ABC Lodge	20/04/2020		Live	5
10005	The Rope club	25/04/2020		Live	5
10006	Tiny Island	26/04/2020		Live	6
10007	Number41 serviced Apartments	27/04/2020		Live	7
10008	402 Morocco inn	28/04/2020		Live	8

I am not sure how to achieve this in Power BI. 

Thanks,

Kanthi.

[Pragati11]

Hi @Anonymous ,

This doesn't help in answering your query. You want to calculate SUM of COUNTS of which column?

Can you please screesnhot some sample data with few rows?

Thanks,

Pragati

[Anonymous]

Hi @Pragati11, 

Updated the query with some 10 sample data and expected solution. I hope this gives an idea, please let me know if it needs further more clarity. 

Thanks,

Kanthi.