Date filter to refresh and set end date as max date from the date table with future date

[mohinisharma_29]

Hello All,

I have a report that has a date filter which need to set by default to last 6 months and automatically refresh the slicer to max date(today). 

I have used below calculation:-

DateDim =

ADDCOLUMNS (

CALENDAR (DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),TODAY()),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"MonYear", FORMAT ( [Date], "mmm" ) & " " & FORMAT ( [Date], "YYYY" )

)

report snapshot below:- 

This above calculation is not refreshing the max date and set it to today. 

Note:- my date table has future dates as well hence, I want to set the max day to automatic refresh based on today.

[v-venuppu]

Hi @mohinisharma_29 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @mohinisharma_29 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @mohinisharma_29 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu]

Hi @mohinisharma_29 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @bhanu_gautam @WillemM  for the prompt response.

If your Date table includes future dates, then TODAY() won't automatically update the slicer to the current date unless you force it.

Here's a simple solution that keeps future dates in your model but limits the default slicer to show only the last 6 months ending with today:

1.Keep your full Date table as-is (including future dates).

2.Create a calculated column in your Date table:

IsInLast6Months =
VAR TodayDate = TODAY()
VAR StartDate = EDATE(TodayDate, -6)
RETURN
IF([Date] >= StartDate && [Date] <= TodayDate, 1, 0)

3.Use this new column IsInLast6Months as a page/filter/slicer filter:

• Add a slicer or filter pane
• Drag IsInLast6Months into it
• Set the filter to “is 1”

If you still want the user to select dates freely:

You can add a separate date slicer

Set its default to today() using bookmarks or relative date filters (Last 6 months)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[mohinisharma_29]

@bhanu_gautam , I tried this approach. However, still this isn't working for my case

[bhanu_gautam]

@mohinisharma_29 , Try using

DAX
DateDim =
VAR StartDate = EDATE(TODAY(), -6)
VAR EndDate = TODAY()
RETURN
ADDCOLUMNS (
CALENDAR (StartDate, EndDate),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"MonYear", FORMAT ( [Date], "mmm" ) & " " & FORMAT ( [Date], "YYYY" )
)

[WillemM]

Hi, You could add Offset columns in your datetable which calculates the differene (in months, days etc) between today and the date in the datetable. Use the offset as a filter in your report/visual...