Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |