Issue getting my Slicers to work on more than one Data Source

[SSGUY]

Hello everyone,

I'm going to try to explain this issue as best as I can. I have tried many different things to solve this issue, but so far I've had no luck. Not sure if its not totally understanding the Star Schema or if it is an issue with the way my data is set up, but I am unable to post much of my data.

I've created a dashboard that is a table, with three differnt slicers on three differing levels, these levels include;

Position Group

Position

Individual

I have two different data tables, one with data from 2022 and one with 2023.

The data with 2022 data has its dates changed to 2023 to try and match what will be done this year, so for example, if there is data that was collected on 1/24/2022, it has been changed to 1/23/2023. This data is being used as a way to look and see what was done last year, and then to try and match it this year. 

With that being said, there is currently no "real" 2023 data coming through into the other data sheet, so I instead put a dummy set in.

The current data connection I have is a connection that goes both ways from date to date, which populates all my data in the way I want it in the table, but the big issue I'm having is that my data filters, which are all pulled from the "2022 Data" only work on the 2022 data. I have tried creating other connections, including creating a Dimension table, but I still couldn't get it to work, it was just filling out the dummy data in the table for every day of the "2022 Data", which I want to remain blank until I get that data in, but the filters worked. I do just want to point out that there are NO dates in the 2023 data set until they come into the system. I'm not sure if I just set this up wrong.

Can anyone help with this or give suggestions on how to set up the schema so that these filters will work for both data sets?

I appreciate any help I can get here.

Thanks

[jaweher899]

One solution could be to create a separate date dimension table that contains all the dates for both 2022 and 2023. This table would be linked to both the 2022 data table and the 2023 data table through a common date field.

Then, when creating the filters in your dashboard, you can reference this date dimension table instead of just the 2022 data table. This way, the filters will apply to both the 2022 data and the 2023 data, even if the 2023 data currently only contains dummy data.

Another solution could be to use the same date dimension table and use a calculated column in the 2023 data table to match the dates with the date dimension table.

You could also try to create a relationship between the 2022 data table and the 2023 data table. This would enable the filters you create in the dashboard to be applied to both tables.

It's also important to make sure that the date fields in both data tables are formatted and labeled in the same way, so that the relationships and filters can be established correctly.

[SSGUY]

So there are already corresponding dates, I have changed the dates in the 2022 data to match with the 2023 data set. Even with these dates connecting the two data sets, The only slicer options I have are for the "2022 data" and "2023 data" sets. I need one slicer that will work on both data sets.

[PowerBIEnthu]

I am not sure, If I understand it right but here are my 2 cents.
1st Approach would be to create a date table with all the possible dates from 2022 data table and 2023 table and link both of the tables with this date table. This should work.
2nd Approach would be use date add to generate the same date current year column in 2022 data table and use lookup value DAX expression to find the related items from the 2022 Data table.
date add will add another column next to your date column for ex 1/4/2022 will have corresponding 1/4/2023 value in the same table, now you can use this column to lookup value from 2022 to check what was done last year on same date.