Date Range does not filter table

[marial16]

Hallo,

i am really struggling with a report i am trying to build.

I have 3 pages. In the first page i am getting data from a SP list to create charts. I have created a YTD/MTD and a date range slicer.

This List has a date field called "DateOfIncident". This is the date i am using.

For the slicers i created a table called 'Dates' :

Date = CALENDAR(MIN(Incidents[DateOfIncident]),MAX(Incidents[DateOfIncident]))

In the second page i added a table that gets data from another list Called 'Performance'. This list has also a column called Month (Date/time).

Tables (of 2 different lists) are connected as i checked in the relationships tab through the Date table i created for the slicers.

Format is the same.

The slicer on the second page does not seem to work.

For example, the table with data from the second list has data from November and December in the Month column.

When i change  the range in the slicer i get no values in the table. I see the data only whren slicer is in full range.

AM i missing something here?

[MikeJohnsonZA]

Hi

For the second table, you need to split the date\time column into two separate columns, one just for the date, and one for the time. Then update your relationship to use the new date column and not the date time.

The reason for this is because '2022-01-13' is not equal to '2022-01-13 08:05' so your slicer can not filter the table.

[marial16]

do i have to manually update the relationship? I couldn't see an option.

DateOfincident as i see is a date time (time included) while Month column is Date only.

[MikeJohnsonZA]

If you don't need the time component at all then you can simply change the DateTime type to Date (It's better to do this in power query) then you should not need to change the relationship.

If you create another column with just the date then you need to delete the old relationship then create a new one between your new date column and the date column in your date table.

[marial16]

I used the Month column in the slicer to filter the second table.

However i aslo have a table with 2 Calculated columns.

At first it seemed like the date range slicer was filtering that table as well.

But now i see that it doesnt.

Do date range slicers work for calculated columns?

[MikeJohnsonZA]

Yes, they will but you want to only be filtering on the date in your date table.

[marial16]

in my calculation when i use MIN and MAX values of my Date table i get correct result but no filtering.

I tried using the Month column in the calculation, but it does not filter..