Skip to content

The Period measure #6

@DaniilMaslyuk

Description

@DaniilMaslyuk

I think the template would benefit from a more elaborate Period measure -- something like this:

Period = 
-- 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'[Date] )
VAR MaxDate         = MAX ( 'Date'[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
)

More details, including the description of parameters and the measure in action, can be found here:
http://community.powerbi.com/t5/Quick-Measures-Gallery/Date-range/m-p/365203

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions