Date range

Member
2000 Views
Highlighted
Member
Posts: 74
Registered: ‎10-15-2016

Date range

[ Edited ]

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
)