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.
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.