Need help with Measure - value before and after 0

[EmaVasileva]

Hi team,

I have the following data:

ID	Date mm/dd/yyyy hh:ss	Item1	Item2
1	1/1/2022 0:00	3900	1000
2	1/2/2022 2:00	4000	1200
3	2/15/2022 11:00	8000	5000
4	2/15/2022 12:00	0	5100
5	5/17/2022 0:00	500	6000
6	6/7/2022 0:00	1000	7000
7	6/7/2022 01:00	1100	7000

I have a value column and sometimes it is possible to have 0 in a row. I need measures which calculate the following:

Measure1: If in Item1 column there is 0, then give me the difference between the previous row value and the value on 1/1/currentyear 0:00 h (8000 – 3900 = 4100). If there isn’t 0 in the column, then give me difference between the latest row 0:00 h and the first row 1/1/current year 0:00.

Measure2:   If in Item1 column there is 0, then give me the difference between the last available row value at 0:00 h and the row with the 0.  (1000 – 0 = 1000). If there isn’t 0, then give me difference between the latest row 0:00 h and the first row 1/1/current year 0:00.

Thank you.

[v-yanjiang-msft · Accepted solution]

Hi @EmaVasileva ,

According to your description, here's my solution.

Create two measures.

Measure1 =
VAR _Pre =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date]
                = MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Date]
                            < MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Item1] = 0 ), 'Table'[Date] )
                    ),
                    'Table'[Date]
                )
        ),
        'Table'[Item1]
    )
VAR _Fir =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = DATE ( YEAR ( TODAY () ), 1, 1 ) ),
        'Table'[Item1]
    )
VAR _MAX =
    MAXX ( ALL ( 'Table' ), 'Table'[Date] )
VAR _LAS =
    MAXX (
        FILTER (
            'Table',
            'Table'[Date] = DATE ( YEAR ( _MAX ), MONTH ( _MAX ), DAY ( _MAX ) )
        ),
        'Table'[Item1]
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( 'Table', 'Table'[Item1] = 0 ) ) > 0,
        _Pre - _Fir,
        _LAS - _Fir
    )

Measure2 =
VAR _Pre =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date]
                = MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Date]
                            < MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Item1] = 0 ), 'Table'[Date] )
                    ),
                    'Table'[Date]
                )
        ),
        'Table'[Item1]
    )
VAR _Fir =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = DATE ( YEAR ( TODAY () ), 1, 1 ) ),
        'Table'[Item1]
    )
VAR _MAX =
    MAXX ( ALL ( 'Table' ), 'Table'[Date] )
VAR _LAS =
    MAXX (
        FILTER (
            'Table',
            'Table'[Date] = DATE ( YEAR ( _MAX ), MONTH ( _MAX ), DAY ( _MAX ) )
        ),
        'Table'[Item1]
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( 'Table', 'Table'[Item1] = 0 ) ) > 0,
        _LAS,
        _LAS - _Fir
    )

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[danextian]

Hi @EmaVasileva ,

• Can't this be done with calculated columns instead of measures? It would be easier to follow this route.
• If there isn’t 0 in the column, then give me difference between the latest row 0:00 h and the first row 1/1/current year 0:00. Whatis latest row and what is first row? Are you referring to the previous and current rows or to rows 1 and 7?