Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
DateSurfer
New Member

Counting rows/incidences between date columns

I have a table of the form: 

 

IDStatusStart_DateEnd_Date
0001Registered01/01/202402/01/2024
0001In-Progress02/01/2024

05/01/2024

0002Registered02/01/2024

04/01/2024

0001Success05/01/2024

20/01/2024

0002In-Progress05/01/2024

05/01/2024

0002Failure05/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! 

 

1 ACCEPTED SOLUTION
speedramps
Community Champion
Community Champion

Click here to download solution

Download PBIX from Onedrive 

 

How it works  ...

 

Create relationships

speedramps_2-1705762143409.png

 

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 ...

speedramps_1-1705762080329.png

 

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.

View solution in original post

1 REPLY 1
speedramps
Community Champion
Community Champion

Click here to download solution

Download PBIX from Onedrive 

 

How it works  ...

 

Create relationships

speedramps_2-1705762143409.png

 

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 ...

speedramps_1-1705762080329.png

 

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.