Counting objects over time with startDate & endDate

[FVZ]

Hi Community,

I would like to get some inspiration/help to create a DAX measure. For my report, I need to display the count of statusses over time, using a start and enddate. Ideally, this should be dynamic. So that it works with both days, weeks and months on the axes.

This is what the data looks like (text and picture format):

status	startDate	endDate
XXX	1-1-2024	29-2-2024
YYY	1-2-2024	15-2-2024
BBB	1-1-2024	30-4-2024
XXX	1-1-2024	31-3-2024

And this is how it should be visualized (example with months, but should work with days & weeks too):

I have difficulty with creating something like this, so it would be great if some of you could help out! If you have any idea, please provide the DAX!

Kind regards

[sanalytics · Accepted solution]

@FVZ 

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

[Anonymous · Accepted solution]

Hi @FVZ 

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.

[sanalytics · Accepted solution]

@FVZ 

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

[Selva-Salimi]

Hi @FVZ 

you can write a measure as follows:

CountStatusesOverTime =

VAR SelectedDate = SELECTEDVALUE('DimDate '[Dates])

 var statusCount = CALCULATE(COUNT(Tbl[Status]) , FILTER((Tbl) , Tbl[StartDate]<= SelectedDate && Tbl[EndDate] >= SelectedDate) )

RETURN

statusCount+0

** you should not have relation between dimdate and tbl and use dates of DimDate in X-axis

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[bhanu_gautam]

@FVZ ,Create a Date table if you don't already have one. This table should include all the dates you want to analyze.

Create a measure to count the statuses over time

Status Count =
VAR SelectedDate = MAX('Date'[Date])
RETURN
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[startDate] <= SelectedDate &&
'YourTable'[endDate] >= SelectedDate
)
)

[FVZ]

Unfortunately I can't get it to work. These are the results I get: 

I also wondered if I shouldn't do something with the relationship between the endDate and Date from my datetable? Right now I only have a relationship between startDate and Date: 

I suppose the measure also needs to be adjusted when I create two relationships.