How to hide the future dates from the slicer conn

[ShuchiSharma]

I have used a date table to connect the data from 2 different files and its working fine. However, when I publish and share the dashboard with other stakeholders, it's gets confusing from them till what date its showing the values. How can I hide the future dates from the slicer?

On the other hand, if there is only 1 excel file and no date table, there is no such problem. 

Please guide!

Thanks

[ShuchiSharma]

Hi guys,

Still need help! Can anyone please help here with the above query? 

Thanks

[ShuchiSharma]

Hi Shishir,

Thanks for the reply. I have tried but seems like doing it somewhere wrong. Can you please have a look?

Using the below, I created the date table:

Date =

VAR MinYear = YEAR ( MIN ( 'Raised Data'[AddDateTime] ) )

VAR MaxYear = YEAR ( MAX ( 'Raised Data'[AddDateTime] ) )

RETURN

ADDCOLUMNS (

FILTER (

CALENDARAUTO( ),

AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

),

"Calendar Year", "CY " & YEAR ( [Date] ),

"Month Name", FORMAT ( [Date], "mmmm" ),

"Month Number", MONTH ( [Date] )

)

And there are only three tables:

Thanks 

[v-yanjiang-msft · Accepted solution]

Hi @ShuchiSharma ,

Firstly, in your below formula, the position of the closing parenthesis of the MAX function is wrong, it should be 

D=CALENDAR(MAX('Date'[Date]),today())

In your new Date table formula, you don't need to calculate the min and max year because the calendarauto function is calculated automatically based on data in the model. Modify it like this.

Date =
ADDCOLUMNS (
    FILTER ( CALENDARAUTO (), [Date] <= TODAY () ),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] )
)

This table will get your expected date period.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ShuchiSharma]

Hi @v-yanjiang-msft Thank you so much. You saved me! Thanks again!

[jaipal]

@ShuchiSharma you can hide future dates by doing this 

[ShuchiSharma]

Thanks for the suggestion jaipal. However, this doesn't include the past dates. I need all the dates till today to compare the results. 

[Shishir22]

Hello @ShuchiSharma,

If you dont need to see future dates data, I would suggest remove it from your date dimension and your fact tables.

You can use Calendar(Max(date column from your fact table), today()) to get table with dates only till today

or

You can add a flag(Calculated column) in your date dimension and use it as a report/visual level filter.

Flag=if(DimDate[Date] <= Today(),1,Blank())

apply Flag=1 as a report/visual level filter.

Please mark it as answer if it solves your issue. Kudos are also appreciated..