Forum Discussion
Date Segmenter
I need to look for a method to put a Date Picker in the reports.
Currently, if I set a fixed start date and end date in the filter, when I upload this report from desktop to online, it remains static, which means that when new data is loaded, the report does not automatically display the most recent dates.
What I need is a dynamic date segmenter that does two things:
1) Automatically update to span from the oldest date to the most recent date available in the uploaded data.
2) Allow the user to manually select a date within that range to view the information they need.
Thus, each time the data is updated, the segmenter's calendar will also reflect the new available date range.
Hi valmenara - you can create a dynamic date picker that meets your requirements using a combination of Power Query, Date Tables, and Slicers.
Create a date table using calculated table with DAX expression and mark the table as Date.The date slicer will always adjust to the range of dates available in your dataset after a refresh.Users can manually select their desired date range within the slicer.The report will remain dynamic and reflect the latest data.
if you want to display the selected range dynamically with text, use below measure:
SelectedDateRange =
"From " & MIN('DateTable'[Date]) & " to " & MAX('DateTable'[Date])Create a dates table that references the min and max dates in your fact table.
CalendarTable = VAR MinDate = MIN('FactTable'[Date]) -- Replace 'FactTable' with your actual fact table name VAR MaxDate = MAX('FactTable'[Date]) RETURN ADDCOLUMNS( CALENDAR(MinDate, MaxDate), "Year", YEAR([Date]), "Month Number", MONTH([Date]), "Month Name", FORMAT([Date], "MMMM"), "Month & Year", FORMAT([Date], "MMM YYYY"), "Year & month", FORMAT([Date], "YYYYMM"), --custom sort for Month & Year "Quarter", "Q" & FORMAT(ROUNDUP(MONTH([Date]) / 3, 0), "0"), "Year-Quarter", FORMAT(YEAR([Date]), "0000") & "-Q" & FORMAT(ROUNDUP(MONTH([Date]) / 3, 0), "0"), "Week Number", WEEKNUM([Date]), "Day Name", FORMAT([Date], "dddd"), "Day of Week", WEEKDAY([Date], 2) -- 1=Sunday, 2=Monday )If this isn't what you're looking for, please define Date Segmenter, include expected out and the reasoning behind.
2 Replies
Hi valmenara - you can create a dynamic date picker that meets your requirements using a combination of Power Query, Date Tables, and Slicers.
Create a date table using calculated table with DAX expression and mark the table as Date.The date slicer will always adjust to the range of dates available in your dataset after a refresh.Users can manually select their desired date range within the slicer.The report will remain dynamic and reflect the latest data.
if you want to display the selected range dynamically with text, use below measure:
SelectedDateRange =
"From " & MIN('DateTable'[Date]) & " to " & MAX('DateTable'[Date])Create a dates table that references the min and max dates in your fact table.
CalendarTable = VAR MinDate = MIN('FactTable'[Date]) -- Replace 'FactTable' with your actual fact table name VAR MaxDate = MAX('FactTable'[Date]) RETURN ADDCOLUMNS( CALENDAR(MinDate, MaxDate), "Year", YEAR([Date]), "Month Number", MONTH([Date]), "Month Name", FORMAT([Date], "MMMM"), "Month & Year", FORMAT([Date], "MMM YYYY"), "Year & month", FORMAT([Date], "YYYYMM"), --custom sort for Month & Year "Quarter", "Q" & FORMAT(ROUNDUP(MONTH([Date]) / 3, 0), "0"), "Year-Quarter", FORMAT(YEAR([Date]), "0000") & "-Q" & FORMAT(ROUNDUP(MONTH([Date]) / 3, 0), "0"), "Week Number", WEEKNUM([Date]), "Day Name", FORMAT([Date], "dddd"), "Day of Week", WEEKDAY([Date], 2) -- 1=Sunday, 2=Monday )If this isn't what you're looking for, please define Date Segmenter, include expected out and the reasoning behind.