Date range
This quick measure returns the selected date range formatted in accordance with the Wikipedia Manual of Style.
The date format can be customized by changing the parameters listed below.
NAME:
Date range
DESCRIPTION:
Write out the selected date range.
PARAMETERS:
Name: Date
Tooltip: Calendar date column
Type: Date field
Name: IsDMY
Tooltip: Date format: TRUE for DMY, FALSE for MDY
Type: Boolean
Default: TRUE (date, month, year)
Name: DayFormat
Tooltip: Day format
Type: Text
Default: "d" (no leading zero)
Name: MonthFormat
Tooltip: Month format
Type: Text
Default: "MMMM" (full month name)
Name: YearFormat
Tooltip: Year format
Type: Text
Default: "yyyy" (four-digit year)
Name: LabelText
Tooltip: Text before date range
Type: Text
Default: UNICHAR ( 128197 ) & " " (calendar icon and a space)
DAX:
Date range =
-- Parameters
VAR IsDMY = TRUE()
VAR DayFormat = "d"
VAR MonthFormat = "MMMM"
VAR YearFormat = "yyyy"
VAR LabelText = UNICHAR ( 128197 ) & " "
-- Formats
VAR DayMonthFormat = IF ( IsDMY, DayFormat & " " & MonthFormat, MonthFormat & " " & DayFormat )
VAR FullYearFormat = IF ( IsDMY, " ", ", " ) & YearFormat
VAR FullDateFormat = DayMonthFormat & FullYearFormat
-- Input dates
VAR MinDate = MIN ( {Date} )
VAR MaxDate = MAX ( {Date} )
-- Formatted dates
VAR StartDate = FORMAT ( MinDate, FullDateFormat )
VAR EndDate = FORMAT ( MaxDate, FullDateFormat )
VAR StartDay = FORMAT ( MinDate, DayFormat )
VAR EndDay = FORMAT ( MaxDate, DayFormat )
VAR StartMonth = FORMAT ( MinDate, MonthFormat )
VAR EndMonth = FORMAT ( MaxDate, MonthFormat )
VAR StartYear = FORMAT ( MinDate, FullYearFormat )
VAR EndYear = FORMAT ( MaxDate, FullYearFormat )
VAR StartDayMonth = FORMAT ( MinDate, DayMonthFormat )
VAR EndDayMonth = FORMAT ( MaxDate, DayMonthFormat )
-- Result
RETURN
LabelText &
SWITCH (
TRUE(),
-- Only one day is selected
StartDate = EndDate,
EndDate,
-- Dates fall within the same month
AND ( StartMonth = EndMonth, StartYear = EndYear ),
IF (
IsDMY,
StartDay & "–" & EndDayMonth,
StartDayMonth & "–" & EndDay
)
& EndYear,
-- Dates fall within the same year
StartYear = EndYear,
StartDayMonth & " – " & EndDate,
-- Dates in different years
StartDate & " – " & EndDate
)
4 Replies
Daniil I like the idea here, but my date column data type is Date/time not Date and I get an error saying that the Min function only accepts a column reference as an argument. I am very new to DAX. Any suggestions?
thanks
Ross
RossChevalier: in this case, it doesn't matter if your Date column is of type Date/Time 🙂 Replace {Date} from my formula above with a fully qualified column reference, like so: 'Date'[Date]. So here's what part of the formula would look like:
-- Input dates
VAR MinDate = MIN ( 'Date'[Date] )
VAR MaxDate = MAX ( 'Date'[Date] )Understood, thank you kindly for the rapid response and education
Great job Daniil!
Also, why future dates don't work, for example when I select range 3/1/2021-11/15/2021?
