Slicer showing me options that I dont yet have data in

[DanielCarvalho]

Hello Comunnity,

I have a dashboard based on my data table and calendar table. They currently have a many to one relationship where my calendar table filteres my data table in a single direction.

I have 2 slicers coming from my calendar table. Fiscal Year and Period(which represents months in the fy).

But on those slicers its showing me fiscal years that I dont have data on my data table yet for example 2025/2026 and so on and my period slicer when I am in the current fiscal year, it shows all 12 periods even though i only have data for one period. As of October 2023, I am in period 1 of fy 2024. I dont want the client to be able to select a period that I do not have data in yet as it makes no sense to do so. I am struggling to correct this as it hasnt happen to me in previous dashboards. I tried changing how the tables are connected but this is still happening.

[Idrissshatila · Accepted solution]

@DanielCarvalho ,

create a new column in your calendar table as following

Current = if('calendar'[Date] <= MAX(YourDataTable[Date]) , "current", "future")

then you filter your slicer with this column where you select current

[Idrissshatila · Accepted solution]

@DanielCarvalho ,

create a new column in your calendar table as following

Current = if('calendar'[Date] <= MAX(YourDataTable[Date]) , "current", "future")

then you filter your slicer with this column where you select current

[DanielCarvalho · Accepted solution]

Thanks for the help, meanwhile I fixed it by adding my already existing date column from my calendar table and doing a relative date filter where i filter last x years including todays date 

[Idrissshatila]

Hello @DanielCarvalho ,

can you show me the calendar formula you used to create the calendar table

[DanielCarvalho]

In this case it is not a calendar created via a formula, its a table loaded up from snowflake, with my companies calendar, including our FY and period options

[Idrissshatila]

@DanielCarvalho does the calendar table has dates for 2025/2026 ?

[DanielCarvalho]

Yes the calendar table is prepared up to 2028. But I want it to show me only dates relevant to my data. And in my data the last available date conected to the calendar table would be todays date

[Idrissshatila]

@DanielCarvalho , that's why its showing these years because they already exists in the table, its a normal performance.

[DanielCarvalho]

Is there not a way I can have those slicers consider the current date and now show any slicer options after the options aplicable to today.

Or have it some how consider my date column in my data table as if the max date is 26/10/2023 its not going to show me years and months after this date?