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 everyone,
I'm working on making a timeline that reflects the headcount of my organization month over month. Because headcount constantly fluctutes, showing an aggregate of all people who are on staff on any given month doesn't necessarily help with trends since it counts both new joiners and attrition at the same time. For this reason, the organization is used to seeing data as of the first of the month.
The data is structured with start and end effective dates. I have a measure in place that is allowing me to calculate totals based on these start and end effective dates, but that doesn't quite work when I take it to the timeline because it aggregates the counts for the whole month.
Is there a good way to built a line chart such that the month shows trending as of the first of the month, rather than the aggregate of all data related to that month? Or is this more of a DAX question?
4. Format the line chart visual to show the headcount as of the first of the month on the y-axis and the months on the x-axis.
3. Drag the "Date" field to the "Axis" field well of the line chart visual.
2. Drag this new calculated column to the "Values" field well of the line chart visual.
This will allow you to see the headcount as of the first of the month for each month instead of the aggregate of all data for that month.
Keep in mind that this is just one way to set up this type of dashboard and that the specifics may vary depending on the data and desired outcome.
Hope this helps!! 🙂
To do this, you can use the following steps:
Yes, creating a line chart that shows trending headcount as of the first of the month can be done in Power BI. The way to do this would be to create a calculated column that uses the start and end effective dates to determine the headcount for each month as of the first of the month. You can then use this calculated column to create the line chart.
Are you able to provide a santized .pbix with your model and current measure? Happy to help.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
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.