Find sum of values for nearest date each previous year

[s-in-a-triangle]

Hi all,

My data has multiple time periods (and multiple rows per time periode). The time periods looks like this:

dates

1-12-2016 00:00

5-12-2016 00:00

12-12-2016 00:00

19-12-2016 00:00

2-1-2017 00:00

6-2-2017 00:00

16-2-2017 00:00

20-2-2017 00:00

27-2-2017 00:00

1-3-2017 00:00

7-3-2017 00:00

16-3-2017 00:00

24-3-2017 00:00

27-3-2017 00:00

3-4-2017 00:00

10-4-2017 00:00

3-5-2017 00:00

8-5-2017 00:00

15-5-2017 00:00

22-5-2017 00:00

9-6-2017 00:00

12-6-2017 00:00

19-6-2017 00:00

18-7-2017 00:00

31-8-2017 00:00

4-9-2017 00:00

12-9-2017 00:00

6-10-2017 00:00

9-10-2017 00:00

10-10-2017 00:00

17-10-2017 00:00

23-10-2017 00:00

30-10-2017 00:00

14-11-2017 00:00

21-11-2017 00:00

27-11-2017 00:00

5-12-2017 00:00

11-1-2018 00:00

15-1-2018 00:00

22-1-2018 00:00

29-1-2018 00:00

6-2-2018 00:00

12-2-2018 00:00

19-2-2018 00:00

5-3-2018 00:00

14-3-2018 00:00

19-3-2018 00:00

27-3-2018 00:00

16-4-2018 00:00

25-4-2018 00:00

29-5-2018 00:00

21-9-2018 00:00

2-10-2018 00:00

16-10-2018 00:00

2-11-2018 00:00

21-11-2018 00:00

26-11-2018 00:00

21-12-2018 00:00

9-1-2019 00:00

18-2-2019 00:00

27-2-2019 00:00

28-3-2019 00:00

10-5-2019 00:00

5-7-2019 00:00

28-8-2019 00:00

30-8-2019 00:00

23-9-2019 00:00

14-10-2019 00:00

15-10-2019 15:03

22-10-2019 11:16

1-11-2019 03:00

15-11-2019 03:00

1-12-2019 03:00

15-12-2019 03:00

1-1-2020 03:00

15-1-2020 03:00

22-1-2020 15:07

23-1-2020 08:08

1-2-2020 03:00

15-2-2020 03:00

1-3-2020 03:00

15-3-2020 03:00

25-3-2020 12:33

1-4-2020 02:00

15-4-2020 02:00

1-5-2020 02:00

15-5-2020 02:00

1-6-2020 02:00

15-6-2020 02:00

1-7-2020 02:00

15-7-2020 02:00

1-8-2020 02:00

15-8-2020 02:00

3-9-2020 11:41

15-9-2020 02:00

1-10-2020 02:00

15-10-2020 02:00

1-11-2020 02:00

15-11-2020 02:00

1-12-2020 02:00

15-12-2020 02:00

1-1-2021 02:00

15-11-2021 02:00

1-2-2021 02:00

15-2-2021 02:00

1-3-2020 02:00

15-3-2020 02:00

I want to compare the last values (i.e. the 15-3-2020 value) in my dataset to the values of the nearest date each year.  With nearest date I mean the closest date to 15/03 each year (can be before or after that date, whichever is closest). I hope to achieve a year on year comparison of values (KPI values are functional for illustration purposes):

	last date 2021 (15-3-2021)	nearest date 2020 (15-3-2020)	nearest date 2019 (28-3-2019)	nearest date 2018 (14-3-2018)	nearest date 2017 (16-3-2017)	nearest date 2016 (1-12-2016)
KPI value	87	90	86	70	95	54

Does anybody know a smart (efficient) DAX or M query? 

I'm stuck at

Column =

CALCULATE (

MAX ( data[SYSDATE] ),

FILTER (

data,

data[Custom] >= data[SYSDATE]

)

)

[v-deddai1-msft · Accepted solution]

Hi @s-in-a-triangle ,

You can use the following calculated column:

Cloest date for each year =
VAR A =
    MAX ( 'Table'[Date] )
VAR B =
    YEAR ( 'Table'[Date] )
VAR C =
    MINX (
        FILTER ( 'Table', YEAR ( 'Table'[Date] ) = B ),
        ABS ( DATE ( B, MONTH ( A ), DAY ( A ) ) - 'Table'[Date] )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            ABS ( DATE ( B, MONTH ( A ), DAY ( A ) ) - 'Table'[Date] ) = C
                && YEAR ( 'Table'[Date] ) = B
        )
    )

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

Best Regards,

Dedmon Dai

[s-in-a-triangle]

Hi @amitchandak ,

Thank you for your quick reply. 

Unfortunately, your solution does not give me the desired result. It gives me the 01/03/2020 value. I'm facing 3 issues:

- Is it possible to get the values for the previous years at once or should I write a measure per previous year? I noticed in your measure "Date(Year(_date)-1" the -1 is hardcoded.

- The nearest date for 2020 is the same date. Your measure states "<=" is it time sensitive? Should I make it dates instead of datetimes? 

- The nearest date could be after today. For instance, when I have values for 02/03/2020 and 17/03/2020 I would like to show the 17/03/2020 value, since that is closest to 15/03(/2021).

Any ideas? Much appreciated.

[amitchandak]

@s-in-a-triangle , You need measure like these for date and value

last year date =
var _Date = maxx(allselected(Table), Table[date])
return
calculate(max(Table[Date]), filter(all(Table), Table[Date] <= Date(Year(_date)-1, Month(_date), day(_date))))

last year value =
calculate(max(Table[value]), filter(all(Table), Table[Date] <= [last year date])