powerbi

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


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


Solution

  • 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] )
        )
    

    enter image description here