Counting objects over time with startDate & endDate

Anonymous 

If I understand your problem correctly, you want to count the number of ongoing statuses between a given start date and end date.

For example:

For status XXX, one started on 1st Jan 2024 and ended on 29th Feb 2024, and another started on 1st Jan 2024 and ended on 31st March 2024.

In January, the count should be 2 since both started in this month.

In February, the count should still be 2 because the first one ends in February, and the second one, which ends in March, is still ongoing.

In March, the count would be 1 as only the second status is ongoing.

For status BBB, starting in January 2024 and ending in April, the count for February, March, and April will be 1 each month, as it continues through all those months.

The same logic applies to weekly calculations.

If this understanding is correct, here’s my suggested solution:

1. Prepare your data: Go to Power Query and generate a list of rows for each status between the start and end dates.
2. Create a Date table: You can create this in either Power Query or using DAX.
3. Write a small DAX.

The complete solution is in the link below.

https://we.tl/t-t7VUBbwner

below screenshot

While I could solve this using DAX, but I always follow Roche's Maxim rule "Data should be transformed as far upstream as possible, and as far downstream as necessary."

If you need a DAX-only solution, please allow me some time to provide it. I must mention that it’s not an easy task.

Hope this helps!

Regards

sanalytics

If it is your solution then please like and accept it as solution

Hi Anonymous 

Thank you very much sanalytics、Selva-Salimi and bhanu_gautam for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

"StatusTable"

Create a Date Table.

DateTable = 
ADDCOLUMNS (
    CALENDAR (MIN('StatusTable'[startDate]), MAX('StatusTable'[endDate])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Week", WEEKNUM([Date]),
    "Day", DAY([Date])
)

Create a mesure.

StatusCount = 
VAR max_d = MAX('DateTable'[Date])
var min_d = MIN('DateTable'[Date])
VAR CountStatuses = 
    CALCULATE (
        COUNTROWS('StatusTable'),
        FILTER (
            'StatusTable',
            'StatusTable'[startDate] <= max_d &&
            'StatusTable'[endDate] >= min_d
        )
    )
RETURN
IF(ISBLANK(CountStatuses), 0, CountStatuses)

Create a Line chart visual.

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.