Forum Discussion

2 Replies

    • gumis_rulez's avatar
      gumis_rulez
      Icon for Helper I rankHelper I
      1 year ago

      The granularity works: D/W/M/Q/Y is a slicer based on the table:

       

      hlp_Dynamic Date Selections = 
UNION(
    ADDCOLUMNS(
        CALENDAR(
            MIN(dimCalendar[Date]),
            MAX(dimCalendar[Date])
        ),
        "Visual Date", [Date],
        "Type", "D",
        "Order", 1
    ),
    ADDCOLUMNS(
        CALENDAR(
            MIN(dimCalendar[Date]),
            MAX(dimCalendar[Date])
        ),
        "Visual Date", 
        [Date] - WEEKDAY([Date], 2) + 1,
        "Type", "W",
        "Order", 2
    ),
    ADDCOLUMNS(
        CALENDAR(
            MIN(dimCalendar[Date]),
            MAX(dimCalendar[Date])
        ),
        "Visual Date", YEAR([Date]) & "-" & MONTH([Date]),
        "Type", "M",
        "Order", 3
    ),
    ADDCOLUMNS(
        CALENDAR(
            MIN(dimCalendar[Date]),
            MAX(dimCalendar[Date])
        ),
        "Visual Date", YEAR([Date]) & "-Q" & CEILING(MONTH([Date])/3, 1),
        "Type", "Q",
        "Order", 4
    ),
    ADDCOLUMNS(
        CALENDAR(
            MIN(dimCalendar[Date]),
            MAX(dimCalendar[Date])
        ),
        "Visual Date", YEAR([Date]),
        "Type", "Y",
        "Order", 5
    )
)

       

      Question is how to combine:

       

      Date Param = 
CALCULATE(
    [Sales],
    KEEPFILTERS(
        DATESBETWEEN(
            dimCalendar[Date],
            [Selected Min Date],
            [Max Date]
        )
    )
)

       

      where

       

      Selected Min Date = 
VAR _MaxDate = [Max Date]
VAR _MinDate = [Min Date] - 1

RETURN

SWITCH(
    'hlp_Date Selection'[Selected Date Selection],
    "1W", _MaxDate - 7,
    "1M", EDATE(_MaxDate, -1),
    "3M", EDATE(_MaxDate, -3),
    "TY", DATE(YEAR(_MaxDate), 1, 1),
    "1Y", EDATE(_MaxDate, -12),
    "ALL", _MinDate )

      and

      Max Date = 
CALCULATE(
    MAX(dimCalendar[Date]),
    REMOVEFILTERS(dimCalendar[Date])
)

      to automatically limit the range. If I choose 3M to limit the displayed data to three months?