Forum Discussion
Show timeline data by first of month rather than aggregate
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?
8 Replies
- giammariam
Solution Sage
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-p/963216 - Sahir_Maharaj
Super User
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.
- Sahir_Maharaj
To do this, you can use the following steps:
- Create a new calculated column that uses the start and end effective dates to determine the headcount as of the first of the month. You can use the following DAX formula to do this: = IF(DATE(YEAR([Start Date]), MONTH([Start Date]), 1) <= DATE(YEAR([End Date]), MONTH([End Date]), 1), 1, 0)
- Drag this new calculated column to the "Values" field well of the line chart visual.
- Drag the "Date" field to the "Axis" field well of the line chart visual.
- 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.
- Sahir_Maharaj
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!! 🙂
- Sahir_Maharaj
- Create a new calculated column that uses the start and end effective dates to determine the headcount as of the first of the month. You can use the following DAX formula to do this: = IF(DATE(YEAR([Start Date]), MONTH([Start Date]), 1) <= DATE(YEAR([End Date]), MONTH([End Date]), 1), 1, 0)
- Sahir_Maharaj
2. Drag this new calculated column to the "Values" field well of the line chart visual.
- Sahir_Maharaj
3. Drag the "Date" field to the "Axis" field well of the line chart visual.
- Sahir_Maharaj
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.
