Datepicker filter

[disney_batista]

I need a date filter that allows me to assign predefined buttons for periods such as: last month, year-to-date, total period, considering the date reference available in the database and not in the current calendar. In addition, the dates must update dynamically as new dates are inserted into the database. So, by default the selection is "last month" where the last date loaded is September 2024, when loading data from October 2024 into the database the selector will automatically update the last month in the selection to October 2024.
Do you know of any way to put this into practice? I used the Powerviz datepicker but it does not meet my needs with the default date

[tomorrowyw]

Hi, you can create a measure to get the latest date in your data:

1.

Latest Date = MAX('YourTable'[DateColumn])

2. Create measures for different date ranges:

Last Month Start = EOMONTH([Latest Date], -2) + 1 Last Month End = EOMONTH([Latest Date], -1) Year To Date Start = DATE(YEAR([Latest Date]), 1, 1) Year To Date End = [Latest Date] Total Period Start = MIN('YourTable'[DateColumn]) Total Period End = [Latest Date]

3.Create a parameter for date range selection:

In Power BI Desktop, go to Home > Enter Data and create a new table:

Date Range

Last Month

Year to Date

Total Period

1. Create a measure for dynamic date filteringDynamic Date Filter = VAR SelectedRange = SELECTEDVALUE(DateRangeSelector[Date Range], "Last Month") RETURN SWITCH( SelectedRange, "Last Month", 'YourTable'[DateColumn] >= [Last Month Start] && 'YourTable'[DateColumn] <= [Last Month End], "Year to Date", 'YourTable'[DateColumn] >= [Year To Date Start] && 'YourTable'[DateColumn] <= [Year To Date End], "Total Period", 'YourTable'[DateColumn] >= [Total Period Start] && 'YourTable'[DateColumn] <= [Total Period End], TRUE )

4

1. Use the filter in your visuals:
   Add the "Dynamic Date Filter" measure to the filter pane of your visuals and set it to "is TRUE".
2. Add a slicer:
   Add a slicer to your report using the DateRangeSelector table

[disney_batista]

Hey @tomorrowyw,

Thanks for the suggestions, I'm trying to implement the suggestion you sent me, but I can't replace the code snippets that mention "YourTable'[DateColumn]" with my date column.

My date column is called 'D_Calendar'[Date].

I get the following return: "Cannot determine a single value for column ''Date'' in table ''D_Calendar''. This can happen when a measure formula refers to a column that contains many values ​​without specifying an aggregation such as min, max, count or sum to obtain a single result."

[Anonymous]

Hi  @disney_batista ,

According to the error message, it seems like what you are trying to create is a measure. The issue is due to the measure formula needing an aggregation function to handle the multiple values in your 'D_Calendar'[Date] column, you can modify your formula to include an aggregation function like MAX, MIN, SUM or AVERAGE etc. on that column...

In addition, please review the following links. Hope it can help you.

Create a relative date slicer or filter in Power BI - Power BI | Microsoft Learn

Custom Date Period Selections in Power BI - BI Elite

Best Regards

[disney_batista]

Hi @Anonymous, Thank you very much for your suggestions!
I got what I needed with the second link you indicated (https://bielite.com/blog/custom-date-period-power-bi/).
However, I would like to know if you can help me with a customization.
When using the custom date filter, if I select the last year, when I use the filter with the predefined period "Last Year" again, the filter does not work, because the custom filter with YTD is still applied.

Do you know if it is possible to define a condition so that when I click again on the predefined periods, the custom filter clears?
Or will I always have to use the manual "Clear selections" function?

[Anonymous]

Hi @disney_batista ,

You can refer the following links to get it by bookmarks and buttons:

Create a button to clear all filters in Power BI 

Create Apply all and Clear all slicers buttons in reports - Power BI | Microsoft Learn

Best Regards