Forum Discussion

jamarston95's avatar
jamarston95
New Member
3 years ago

Calculate difference in value between consecutive dates

Hi all,   I have a table that looks something like this and I want to create a measure which will calculate the difference (NOT % difference) between similar rows but with the previous date.   ...
ERD's avatar
ERD
Community Champion
3 years ago

Hi. There are multiple ways to achieve this. Here is an example:

res = 
VAR prevValue =
    CALCULATE (
        MAX ( T1[Value] ),
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( T1 ),
                T1[Date] < MAX ( T1[Date] ) && T1[Geography] = MAX ( T1[Geography] ) && T1[Variable] = MAX ( T1[Variable] )
            ),
            T1[Date]
        )
    )
RETURN
    IF (
        NOT ISBLANK ( prevValue ) && ISINSCOPE ( T1[Date] ),
        MAX ( T1[Value] ) - prevValue
    )

 

jamarston95's avatar
jamarston95
New Member
3 years ago

Hi ERD ,

 

Thanks for your response. I do have several other variables that I didn't include to simplify the question. I've altered your measure by replicating and adding: 

&& T1[Variable] = MAX ( T1[Variable] )

 for each extra variable in the FILTER expression. However, when I try this approach it is currently producing a blank value. It would be helpful if you could explain how prevValue is calculated? 

 

My understanding is that the CALCULATE function is evaluating the max value, based on the top 1 date, filtered such that the date isn't the latest date. My confusion arises here:

&& T1[Geography] = MAX ( T1[Geography] ) && T1[Variable] = MAX ( T1[Variable] )

 Could you explain what this is doing?

 

Thank you