Using a date slicer to dynamically identify period types in calculated function

[SMG]

Summary: I am trying to use a date range slicer to dynamically update a calculated column in a table.

Background: I do a lot of comparisons between a period in time to the corresponding period in time the year prior. So for example, if I have a table that has dates ranging from 1-Jan-20 till today, I want to do a comparison between 15-Nov-23 and 15-Dec-23 to the same period the year prior, so I can compare how I did to the previous year.

That means I need to identify what is This Period [TP] and what is Last Period [LP]. This Period [TP] is defined by the date ranges in a slicer, and Last Period [LP] is automatically calculated as a result, and exclude all values from my card that are not identified as [TP] or [LP].

[Anonymous · Accepted solution]

Hi @SMG 

You can create several measures as follow.

PERIOD = 
VAR _min =
    MIN ( 'Date'[Date] )
VAR _max =
    MAX ( 'Date'[Date] )
VAR _minl =
    DATE(YEAR(_min) - 1, MONTH(_min), DAY(_min))
VAR _maxl =
    DATE(YEAR(_max) - 1, MONTH(_max), DAY(_max))
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) >= _min
            && SELECTEDVALUE ( 'Table'[Date] ) <= _max,
        "TP",
        IF (
            SELECTEDVALUE ( 'Table'[Date] )
                >= _minl 
                && SELECTEDVALUE ( 'Table'[Date] )
                    <= _maxl,
            "LP",
            "NA"
        )
    )

TP = CALCULATE(SUM('Table'[AMOUNT]), FILTER('Table', 'Table'[PERIOD] = "TP"))

LP = CALCULATE(SUM('Table'[AMOUNT]), FILTER('Table', 'Table'[PERIOD] = "LP"))

Is this the result you expect?

Best Regards,

Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sjoerdvn]

DATE(YEAR(_max) - 1, MONTH(_max), DAY(_max))

Really? Did you ever consider leap years?

[sjoerdvn]

have you tried https://learn.microsoft.com/en-us/dax/sameperiodlastyear-function-dax