cancel
Showing results for 
Search instead for 
Did you mean: 

Date range

Member
2528 Views
Highlighted
Daniil Member
Member

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
)