Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need help with Measure - value before and after 0

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.

Need Help
Show and Tell
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    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.

     

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    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's avatar
    danextian
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    • 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?