Counting rows/incidences between date columns

DateSurfer — Fri, 19 Jan 2024 16:00:40 GMT

I have a table of the form:

ID	Status	Start_Date	End_Date
0001	Registered	01/01/2024	02/01/2024
0001	In-Progress	02/01/2024	05/01/2024
0002	Registered	02/01/2024	04/01/2024
0001	Success	05/01/2024	20/01/2024
0002	In-Progress	05/01/2024	05/01/2024
0002	Failure	05/01/2024	20/01/2024

And so on.

I would like to plot count vs date, where for each date, we count the number IDs on that date split by status (Registered vs In-Progress vs Success vs Failure). The user would choose to filter when to start counting from (ex: only count IDs in the last 7 days, 6 months, one year) through a slicer on the side. Call this user_start_date.

Caveat: The formula for the first two (registered and in-progress) is different for the formulas for the last two (success and failure)

Registered_Count = COUNT(Status == "Registered" & start_date <= date < end_date)

but

Success_Count = COUNT(Status == "Registered" & user_start_date <= start_date <= date < end_date)

This is done so that - if the user only wishes to see IDs in the last 7 days, we do not view IDs that have already been successful (or failed) before the 7 days (on the other hand we still wish to see those that are still Registered/In-Progress).

In-Progress_Count has the same formula as Registered_Count (but Status == Registered) and Failure_Count has the same formula as Success_Count (but Status == Failure).

I think the first two should be easier (although I am new to DAX so still don't know how to implement it), but the latter two are based on user filtering. I think given the user's slicer selection, you would want to filter the table so that start_date >= user_start date first and then apply the formula. But I don't know how to do this. Help would be appreciated.

I have set up an active relationship between an AUTOCALENDAR() table and the start_date, and an inactive relationship between the calendar and the end date. But not sure how to leverage this!

Re: Counting rows/incidences between date columns

speedramps — Sat, 20 Jan 2024 14:50:35 GMT

Click here to download solution

Download PBIX from Onedrive

How it works ...

Create relationships

Create dax measure ...

ValidCount = VAR mindate = MIN('Calendar'[Date]) VAR maxdate = MAX('Calendar'[Date]) VAR mysubset = FILTER(Facts,Facts[Start_Date] <= maxdate && Facts[End_Date] <= mindate) RETURN CALCULATE( DISTINCTCOUNT(Facts[ID]), mysubset )

Create report ...

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos.

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button. Thank you.

topic Re: Counting rows/incidences between date columns in DAX Commands and Tips

Counting rows/incidences between date columns

Re: Counting rows/incidences between date columns