How to filter a table based on a particular date in power BI

Question

I have the below table in power BI and I want to perform 2 things:

1. I want to filter the table based on date. I.e, I want to filter only 01-Jan values.
2. Once I get the 1st Jan values I want to calculate the difference between 2 dates.

Below is what I have:

Date	Value
1-Jan-2020	5.5
2-Jan-2020	6.2
1-Jan-2021	14.2
2-Jan-2021	3.4

. .

I want to achieve below:

Date	Value	difference
1-Jan-2020	5.5	Difference between value col [14.2-5.5]
1-Jan-2021	14.2

---

Below is the table of values I have got after adding a column.

**NAV	Date	Column **
360.9914	Monday, January 1, 2024	y
312.3794	Monday, January 2, 2023	y
277.4562	Monday, January 3, 2022	y
190.9738	Friday, January 1, 2021	y
139.8156	Wednesday, January 1, 2020	y

Now I want to calculate the difference between the NAV column rows, with the current measure it is working like below:

**Nav_Diff_Measure	Column	Year**
	y	2024
49.33	y	2023
35.49	y	2022
	y	2021
51.16	y	2020

Expected Output is :

**Nav_Diff_Measure	Column	Year**
	y	2024
49.33	y	2023
35.49	y	2022
87.24	y	2021
51.16	y	2020

Issue is that it is not calculating difference for 2021 year.I would like to show negative values as well if applicable, as I'm planning put a data bar for this value.Below is the image link where the table image is clear.Kindly help to get the negative values as well.

[Current working image][1] [1]: https://i.sstatic.net/fzC5W4h6.png

Answer 1

pls try this

measure =
VAR _next =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] > MAX ( 'Table'[Date] )
                && 'Table'[Column] = "y"
        ),
        'Table'[Date]
    )
VAR next_value =
    SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Date] = _next ), 'Table'[NAV] )
VAR _last =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Column] = "y" ), 'Table'[Date] )
RETURN
    IF (
        MAX ( 'Table'[Date] ) = _last,
        BLANK (),
        next_value - SUM ( 'Table'[NAV] )
    )