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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

occurances on the same date

Hey guys, i have been struggling with this problem for a little while so hopefully someone here can help me. I havent been using power BI alot so maybe the anwser i quite ovious, however thanks for any input.

 

I have a dataset containing activity ID, start date and end date. My goal is to find out if the same activity happens several times in the same timeframe. My datasett looks like this:

 

Activity ID Start date End date
100 01.01.2020 02.01.2020
200 04.01.2020 06.01.2020
200 05.01.2020 06.01.2020
300 10.01.2020 14.01.2020
200 15.01.2020 20.01.2020
100 20.01.2020 25.01.2020
300 26.01.2020 28.01.2020

 

 

As you can see activity 200 happens two times in the same periode (5. - 6. of januar, row 2 and 3). A possible solution would be to use DAX and make a calculated collum which returs the number of occurances. 

The resultat would for example look like this:

 

Activity ID Start date End date Occurances
100 01.01.2020 02.01.2020 1
200 04.01.2020 06.01.2020 2
200 05.01.2020 06.01.2020 2
300 10.01.2020 14.01.2020 1
200 15.01.2020 20.01.2020 1
100 20.01.2020 25.01.2020 1
300 26.01.2020 28.01.2020 1

 

However this is just a solution, if you have a better one, feel free to share 🙂 

 

Once again, thanks for any input. 

Kind regards.

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , Try a new column like

countx(filter(Table, [Activity ID] = earlier([Activity ID]) && [End date] >=earlier([start date]) && [End date] <=earlier([End date])),[Activity ID] )

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
harshnathani
Community Champion
Community Champion

HI @Anonymous ,

 

Not very clear.

 

Try creating a Calculated Column

Occ = COUNTROWS(FILTER('Table','Table'[Activity ID] = EARLIER('Table'[Activity ID]) && 'Table'[End date] = EARLIER('Table'[End date])))

 

Regards,

Harsh Nathani

AllisonKennedy
Super User
Super User

I don't know fully what you want yet.
What is a 'period'?> From your example I'm guessing days?
Do you want to know if it starts, ends or just any overlap in that period?
You could just create a report with date filter and use DAX to:
ActivityOccurances = COUNT(Table[Activity ID])

You should use a date table though, and depending on your answer to my questions will need to update the measure or something to make sure everything you want to see is counted, such as:

ActivityCount =
VAR _startPeriod = MIN(Date[Date])
VAR _endPeriod = MAX(Date[Date])
RETURN
COUNTROWS(FILTER(Table,
(Table[Start Date] > _startPeriod && Table[Start Date] < _endPeriod)
|| (Table[End Date] > _startPeriod &&
Table[End Date] < _endPeriod)
))

And make a matrix with Activity ID in rows and Activity Count in values.

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Thank you!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors