Hoping somebody can help.
I am in the process of creating a company dashboard, that inludes both company level financial data and employee performance data. We have pulled the data from our CRM, aggregated inside a seperate SQL database that effectively pulls from the live system. We have then created views that further aggregate the various metrics.
Because of the different reporting calendars we work to, i.e. calendar month for net new bussiness, a temp calendar for employees sales, and a delayed invoicing month we have seperate timeframes that we have then assigned to a month name so that they might be compared. In order to do this, all tables are related to a single date table that creates the relationship between all tables. Which is working perfectly. However.....
Because of the format of the date that we are using, for example "monthYear" as text, it is not recognised by power bi as a date and subsequently we are having sort columns by a month number to ensure they are in order. What this means however, is that unless i put a visual filter on a chart, it will displayed all "monthYear" sorted by "monthNumber" in this case 1-12, for each year. Meaning there are mutliple duplicate 1s and 2s and 3s and so on. This leads to a chart that shows multiple years of data on the same chart. I can add a visual filter by "date" which is recognised as a date and allows me to filter the current year of data, and this works. Unfortunately however, when i subsequently select a different year to view on the slicer, the chart will not show it, as it is filtered to show current year only.
Not sure if there is a way i can avoid this issue by adding a default slicer value i.e. current year, so i dont need to apply a visual filter. Or if there is a way i can force the slicer to supersede the filter?
Any advice or guidance much appreciated!
