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 a...
AllisonKennedy
Community Champion
Community ChampionI 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
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
Thank you!