How to get the relative date this year and a year ago

[Anonymous]

Hi everyone,

Hope you are all well.

I used the following two calculations to get my Date Numbered column:

MaxDate = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))

DateNumbered = CALCULATE(DATEDIFF(SELECTEDVALUE('Table'[Date]),[MaxDate],DAY)+1)

It worked fine when I just had 2020 dates but now I have 2020 & 2019.

I need the table below to do 1-15 for 2019 and 2020 does anyone have any ideas?

Thanks in advance!

Lauren.

[DataZoe · Accepted solution]

@Anonymous I think this measure may give you the correct result (edited to change the switch year to SELECTEDVALUE() instead of MIN())

DateNumbered =
VAR _maxDate_2020 =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        ALL ( 'Table'[Date] ),
        YEAR ( 'Table'[Date] ) = 2020
    )
VAR _maxDate_2019 =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        ALL ( 'Table'[Date] ),
        YEAR ( 'Table'[Date] ) = 2019
    )
VAR _dayspast =
    SWITCH (
        YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ),
        2020,
            CALCULATE (
                DATEDIFF ( SELECTEDVALUE ( 'Table'[Date] ), _maxDate_2020, DAY ) + 1
            ),
        2019,
            CALCULATE (
                DATEDIFF ( SELECTEDVALUE ( 'Table'[Date] ), _maxDate_2019, DAY ) + 1
            ),
        BLANK ()
    )
RETURN
    _dayspast

[Anonymous]

@DataZoe that has worked perfectly thank you!

[amitchandak]

@Anonymous , Not very clear, Try like

MaxDate =
var _max CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
return
date(year(_max)-1,month(_max), day(_max))

DateNumbered = CALCULATE(DATEDIFF(SELECTEDVALUE('Table'[Date]),[MaxDate],DAY)+1)

[Anonymous]

Hello @amitchandak ,

Thank you for your quick response and your help.

I am now getting the following: