Forum Discussion

Lullabit13's avatar
Lullabit13
Frequent Visitor
1 year ago
Solved

Time intelligece with non-continuous special dates

Hello! Need some help with modeling special dates.

I have a fact table called "Sales" and a calendar table.
On the Sales table, I have various measures such as Sales PY and Sales YTD.

Now, I need to calculate measures but only for special dates, such as summer discount dates.
Since a particular date in the year can have more than one type of special date (e.g., both summer and Christmas discounts at the same time), I cannot add a column in my calendar dimension. Additionally, the dates vary from year to year. For example, in 2024, the summer discount period might be from 01-01-2024 to 01-03-2024 while in 2025, it could be from 01-02-2025 to 01-07-2025

Therefore, I created a table that lists specific dates and the special event they correspond to.
The relationship between the special dates table and the calendar is N:1 with a bidirectional filter in both directions.

The problem arises when trying to display measures using time intelligence in a matrix.
I have a matrix where the rows follow a date hierarchy, and the columns contain Sales, Sales PY, and Sales PY Summer.

Sales py summer =

CALCULATE (
[SALES],
SAMEPERIODLASTYEAR(CALENDAR[DATE]),
CALENDAR_SPECIAL[NAME] ="SUMMER"
)
The measure works, but if I add it to a matrix and have a slicer on the same page filtering by CALENDAR_SPECIAL[NAME], the visual breaks because SAMEPERIODLASTYEAR requires continuous date periods. Same with others time intelligence functions.

Is there another way to define this measure, or should I change the model?

Need Help
  • DataInsights's avatar
    DataInsights
    1 year ago

    Lullabit13,

     

    Try removing the relationship between CALENDAR and CALENDAR_SPECIAL, and use DAX to apply filters. Example measures:

     

    SALES PY =
CALCULATE ( [SALES], SAMEPERIODLASTYEAR ( CALENDAR[DATE] ) )
    Sales PY Summer =
VAR vSpecialDates =
    TREATAS (
        CALCULATETABLE (
            VALUES ( CALENDAR_SPECIAL[DATE] ),
            CALENDAR_SPECIAL[NAME] = "SUMMER"
        ),
        CALENDAR[DATE]
    )
VAR vResult =
    CALCULATE ( [SALES PY], vSpecialDates )
RETURN
    vResult

     

    Here's a dynamic version that captures the CALENDAR_SPECIAL slicer selection:

     

    Sales PY Special Dates =
VAR vSpecialDates =
    TREATAS ( VALUES ( CALENDAR_SPECIAL[DATE] ), CALENDAR[DATE] )
VAR vResult =
    CALCULATE ( [SALES PY], vSpecialDates )
RETURN
    vResult

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    1 year ago

    Lullabit13,

     

    Try removing the relationship between CALENDAR and CALENDAR_SPECIAL, and use DAX to apply filters. Example measures:

     

    SALES PY =
CALCULATE ( [SALES], SAMEPERIODLASTYEAR ( CALENDAR[DATE] ) )
    Sales PY Summer =
VAR vSpecialDates =
    TREATAS (
        CALCULATETABLE (
            VALUES ( CALENDAR_SPECIAL[DATE] ),
            CALENDAR_SPECIAL[NAME] = "SUMMER"
        ),
        CALENDAR[DATE]
    )
VAR vResult =
    CALCULATE ( [SALES PY], vSpecialDates )
RETURN
    vResult

     

    Here's a dynamic version that captures the CALENDAR_SPECIAL slicer selection:

     

    Sales PY Special Dates =
VAR vSpecialDates =
    TREATAS ( VALUES ( CALENDAR_SPECIAL[DATE] ), CALENDAR[DATE] )
VAR vResult =
    CALCULATE ( [SALES PY], vSpecialDates )
RETURN
    vResult