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

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions