Filter - now Date but I want Year

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
I have a Matrix table.
There are two Tables, one Fact and another Date and I already linked up so SAMEPERIODLASTYEAR is working
I have a problem with Filter Visual in which I want to put 2016, 2017, 2018 buttons which are the three years in FACT table.
However, the date in FACT table is date not year so they appear as 3 Jan 2016 etc instead of 2016.
My Date table has Year column but it goes to 2025.
I tried dragging Date Table's Year to Filter Visual and the buttons are 2016, 2017,... 2025, more than what I expected.
I only want years that is related to data in FACT table only, ie, only 2016,17 & 18

What should I do ? Create One more Column of Year in FACT table query ?
Is there another way we can use Date table to achieve the same ?
Thanks
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You can create an additional table containing just the years found in the fact table:

=<br><span class="Keyword" style="color:#0070FF">SUMMARIZE</span><span class="Parenthesis" style="color:#969696"> (</span> FactTable, 'Date Table'[Year] <span class="Parenthesis" style="color:#969696">)</span><br>

then link this table via a one to many relationship with the Date Table using the Year. Then use the year column in this new table in the slicer. In this case you would hide the Year column within the Date Table:

kghbfsv.png



Or you can limit the rows of the date table to only the years found in the fact table during the ETL process
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top