Recently, a friend of mine came across with an interesting request he got from his client. They needed to see values for the specified period in the past, based on dates selection.

For example, if they select March 31st, 2020, they need to see values for the previous 12 months, so starting from April 1st, 2019. Additionally, they need an option to choose a specific date and see values from the beginning of the year for the selected date. Again, if they select March 31st, 2020, they need to see values starting from January 1st, 2020. Visuals would be adjusted to dynamically change based on user selection.

That being said, Edit Interactions, as a way to remove the filter from specific visual, was not an option, since it would completely deny a user from selecting any specific value for filtering.

Tricky part

The trickiest thing here is establishing a proper relationship between the Date dimension and the fact table. Because, if you rely on the “normal” relationship between the Date dimension and fact table, data will be filtered based on dates selection which serves as a relationship between these two tables.

Let’s head over to Power BI Desktop and see if something can be done.

#power-bi #data-science #data-visualization #data-modeling #towards-data-science