Sliced Table Does Not Display All Results

[BSWJON]

Please could you help answer this (probably) basic issue:

1. I have a table with headings as below
2. I have slicers for site, year, date, LTA type
3. When using the slicers to refine the data, the data displayed is missing 99% of the rows- so when I refine by year 2021, for example there should be 20 rows of data, instead it shows 1 result, incidentally this is the most recent entry.

Notes

1. The slicers are not linked to any other report connections
2. The slicers are not linked to any macros
3. The data is not filtered in any other way
4. The table does not source data from elsewhere
5. The 'year' data is =DATE and formatted to YYYY.

Any help on this matter would be most appreciated!

Site	Date	Month	LTA Type	Incident Type	Employee(s) Involved	Nature of Injury / Damage	Brief Description of Incident	Action Taken	Year
BOG	30/6/21	="","",[@Date])]Jun	N_A						="","",[@Date])]2021
FTW	22/7/21	="","",[@Date])]Jul	N_A		N/A	None			="","",[@Date])]2021

 

[Anthony47]

The 'year' data is =DATE and formatted to YYYY.

Don't do that, use the YEAR function, as in '=IF([@Status]<>"",YEAR([@Status]),"")

Bye

 

[BSWJON]

Thanks for the input Anthony, I'm afraid that didn't work though. I've tried the following formula also:

=IF([@Date]="","",YEAR([@Date]))

However this returns 1905 rather than the actual year. It is strange as in other worksheets the formula works fine?

 

[Anthony47]

Of course you had to adapt the formula to your table structure, and you did it correctly.
If the resulting Year is wrong, then I suspect that the column "Date" doesn't contains a true date: if you format the column as "number with two decimals" what do you read?

Anyway, how the slicer workde after you created a value in under Year? (non only a formatted view)

Bye

 

[BSWJON]

Yeah I did adapt it to no avail.

I've changed my exisiting formula to reference the cell rather than the range name, (so instead of [@Date] it is now C9) and the formula now works fine.

Just an idiosyncrasy of excel i guess?