Forum Discussion
Show applicants on all dates
Hi. Im new at BI and dont really know how to start with this problem.
I have a very simple list with a name, a start date and a stop date. I want to have a diagram thats shows the amount of people on every day from the first date to the last date in the list.
example:
Person1 startdate 16-02-01, stopdate 16-02-10
Person2 startdate 16-02-01, stopdate 16-02-07
Person3 startdate 16-02-05 stopdate 16-02-11
the diagram would show all dates from 16-02-01 to 16-02-11 with the amount of people on each day
This is "relatively" difficult because the data isn't a natural fit to the "easy way" of using power pivot/power bi. I suggest two tables that are not connected. The table you mention (I will call data) and a second Calendar table that has a list of all your dates. Then create a measure in the calendar table something like this.
Count of People = countrows(filter(data),
Data[startdate] <= max(Calendar[Date]) &&
Data[enddate] >= max(Calendar[Date])
)
Put the date column from your calendar on a chart axis and the new measure in values.
2 Replies
- MattAllington
Community Champion
This is "relatively" difficult because the data isn't a natural fit to the "easy way" of using power pivot/power bi. I suggest two tables that are not connected. The table you mention (I will call data) and a second Calendar table that has a list of all your dates. Then create a measure in the calendar table something like this.
Count of People = countrows(filter(data),
Data[startdate] <= max(Calendar[Date]) &&
Data[enddate] >= max(Calendar[Date])
)
Put the date column from your calendar on a chart axis and the new measure in values.
That's a great solution MattAllington !!
magnus_jansson here a sample output from the Matt's solution.
