Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Difference between values in same column

Hi

 

I'm trying to display the difference between values in a table. I have searched around and viewed PREVIOUSMONTH function etc but the way my data is structured there are gaps in dates between emails being sent out - so it needs to pick up the last known send. See screengrab of what I am after (desired output column):

 

I have email statistics data and have created a measure to produce the unique clicks of each email. I can drop into a table and show when the email was sent, the name and the click rate using the measure. However, I need to show the change in click rate consecutively as each email goes out as and when.

 

Is there DAX code that will support this?

 

Thanks

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous ,

    You need create an Index column in Query Editor and create a measure with the formula below.

    Measure =
VAR a =
    CALCULATE (
        [click_reate],
        FILTER (
            ALLSELECTED ( 'Table1' ),
            'Table1'[Index]
                = MAX ( 'Table1'[Index] ) - 1
        )
    )
RETURN
    IF ( a = BLANK (), BLANK (), 'Table1'[click_reate] - a )

    Here is the output.

    Best Regards,

    Cherry

     

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Anonymous ,

    You need create an Index column in Query Editor and create a measure with the formula below.

    Measure =
VAR a =
    CALCULATE (
        [click_reate],
        FILTER (
            ALLSELECTED ( 'Table1' ),
            'Table1'[Index]
                = MAX ( 'Table1'[Index] ) - 1
        )
    )
RETURN
    IF ( a = BLANK (), BLANK (), 'Table1'[click_reate] - a )

    Here is the output.

    Best Regards,

    Cherry

     

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Anonymous ,

    Have you solved the problem?

    If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

    If you still need help, please feel free to ask.
    Best Regards,

    Cherry