I was helping someone recently with an issue. They wanted to allow end users (department managers in this case) to enter data in a table as simply as possible, and then use this data for reporting. The challenge often is, that the easiest way to enter data from a user’s perspective doesn’t always give you what you need from a reporting and analytics perspective. Let me show you what I mean below.
The table on the left above shows the change in head count in each department, and is to be populated by the manager. But when it comes to reporting, we really need to know the total change in headcount as a number for each year, not just the first year the change occurred (as shown in the table to the right, above).
There are different ways to solve this problem, but I decided to do it using a combination of Power Query and DAX. The resulting DAX formula is a form of custom time intelligence. You can read more about custom time intelligence in my article here.
The easiest way for me to show you my solution is to do a demo. You can watch that in the video below.
Video
Learn more at Skillwave.Training
If you liked this lesson, you will find almost 50 hours of premium Power BI learning video content available at Skillwave.Training including many free preview lessons.
Every solution is different depending on your data. Depending on your data, I would load a separate table with the starting headcount and use DAX to add the 2 sets of numbers together.
Good article, but what about when we have a starting base head counts for each departments? In the way you shown, you was only performing the running total of the increment units, but basically in real scenario we have a starting situation of the heads count (starting heads count in 2021)
Thanks Matt!
That is awesome. Can you suggest how to deal with same scenario but when Year is a Slicer and in the Matrix, we see Sub-Department (on Columns) and Department (on Rows)? Basically like when we do not have Numeric (like Year in this case) in the output visual.
Regards,
Bharath
Are you saying you want to select a year from a slicer, such as 2023 in my sample, and see the numbers 1,2,6,1? I can’t see why that wouldn’t work using the same approach.