Count number of occurrences of each "phase" per day

[Dan492813]

I want to be able to say for each particular day of the year how many instances of a propery phase is open. 

I have a table of property ID's that gives which phases the property has been through over time. For example:

Using the st_date & end_date I want to be able to show, for instance, on the 30/06/2022 how many properties were in each phase.

There are about 30 phases across the business so on the 30/06/2022 I might have:

3 properties in "awaiting work book"

7 properties in " Asbestos inspection"

4 properties in "TORT"

I'd expect to see about 150 properties spread over different phases. 

My problem comes from "on this date" as I only have the start date and end date to work from. I then also need to be able to show for this date "this" many properties were in each of these phases. 

This is the kind of view I'm trying to get to but for this I'm using the start date on the x axis not the actual count:

Thank you in advace for any help, Dan

[daXtreme]

@Dan492813 

Since the date ranges are very small (I can see a max of 20 days in the table), it'll be best if you expand the ranges into individual days. So, instead of just keeping the range st_date-end_date, expand the row into individual dates. This can be done in Power Query and in many different days; for instance, you can have a separate table with just prty_id and date columns and join it on prty_id to the table above and the calendar table (you do have a calendar, don't you?) on date.. Once you've got this, it's very easy to create a measure that'll do what you want. Namely,

[# Properties] = distinctcount( YourTable[prty_id] )

Note that this measure will correctly aggregate as well across statuses. You have to decide what you want to calculate if you select not a single date but several of them. If there's a property in the current context that has 2 different states on these dates and you don't partition by states, it'll be counted only once. On the other hand, if you slice by status as well, it'll be counted for each status that it was in on the particular date.

[Dan492813]

How do I create a seperate table for this? 

[daXtreme]

By using Power Query.

Solved: Expand a date range - Microsoft Power BI Community

powerquery - Power Query transform a table with date ranges into daily - Stack Overflow

[Dan492813]

I tried to find some simple sample data but I have tens of thousands of records with larger amounts of phase lengh.

The seq_no is also a count of how many times it has been through the empty phase:

So this property the second time it was empty was 2014:

I have 13K properties that in have been through multiple phases each time they have been empty. Over 100k empty proprty records. Probably over 500k phases

[daXtreme]

Well, it does not matter at all. Just create a separate table with |prty_id|date| the way I described. Connect it as I said and create the measure. PBI can handle hundreds of thousands of rows without a problem as long as the cardinality of the columns are low. And in your case this should be so. In the table you can also include statuses for each day. The gist is that you have to expand these date ranges. Then everything becomes easy (and fast). 

By the way, here's a guide about how to create a good model, that is a model in which DAX will be optimized (even for a large volume of data) -> Understand star schema and the importance for Power BI … (bing.com)