Forum Discussion
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
- AllisonKennedy
Community Champion
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 - amitchandak
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] )
