I have a transaction table that has a "TransDate" field.
and I created a date table that has dates starting from "1/1/2019" till "Today".
and I linked the transaction table with the date table using a "DateKey" field which is an integer representation of the "TransDate"
for example: "TransDate = 1/12/2019.....the date key will look like this: "1122019".
In my transactions table, it has transactions that are older than 2019 (older than my date table, which as I specified above it starts from 1/1/2019)
I used a slicer and the value for the slicer is the date of the "Date table", so the starting point of the slicer is 1/1/2019 till today.
My issue is.....when I set the slicer to start from the first starting date (which is 1/1/2019 in this situation), it does not filter the other visuals. So the older transactions (older than 2019) does appear and I don't want them to appear.
but once I make the starting date in the slicer as the next day, it does filter and work.
Is this a bug? tried making my date table start with different dates other than 1/1/2019 but it always gives me the same results.
the starting date does not filter while the next day does.
So I'm not sure if this would be considered a bug or by design. But what happens with slicers if you select every value in a column is that Power BI detects this and skips these slicers. (because normally selecting everything is the same as not filtering at all) It's assuming that you have all the values linked and if you don't that it's probably a mistake.
How can I fix this?
I think you'll find that if you just add values to your date table that cover all the possible dates in your transaction table then the slicer will start working as expected.
Or if you really don't want to see any data prior to 2019 then you could filter this data out of your transaction table.
I would say you are partially correct. I have Order data in one table from 1/1/21 but my Calendar table only goes back to 5/1/21. If I set the Start Date to 5/1 it counts the older records regardless of what my end date is. So if I have my end date set to 5/2 or today (6/22) it still counts older dates. Setting it to 5/2 means that ALL records are NOT selected. So it's ignoring the start date if set to the min date. Curious...if I use my mouse and nudge start date circle on the slicer a bit right...it still shows 5/1 but now filters the old records. Using the date picker always shows the older ones. I'll call this a bug.
So I went a step further and loaded data into my calendar for 4/21 and put a filter on the slicer to only show 5/1 and greater. Same behavior.
The only safe option at this point would be making sure the Order table doesn't have any dates older than what I want end users to see.
Hello d_gosbell and thanks for your time and reply.
The thing is the report has different transaction tables which all are connected to the date table so I didn't make the date table with values specific to a single transaction table. And I need the old transactions for other measurements.
The solution i'm using now is having the date slicer starts a day before 1/1/2019 (31/12/2018) and I will ask the users to not drag the starting date pointer in the slicer to the begining 🤷
I hope they can fix this since it looks wrong. the slicer says 1/1/2019 to today and the report showing older transactions 🤷