Forum Discussion
Datepicker filter
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
- 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
- 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". - Add a slicer:
Add a slicer to your report using the DateRangeSelector table
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."
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
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?
