Dynamically Calculating Difference between two rows based on filter selected

[TMAUND123]

I currently am working on a dashboard where i need a chart to show the difference in time between multiple months (see above).

I have created a column using the following expression to get the difference between the row values in the AverageTimeTakenInMSPerSearch.

Difference = vwActivityLogSummarySearchStats[AverageTimeTakenInMSPerSearch] - IF(
vwActivityLogSummarySearchStats[AverageTimeTakenInMSPerSearch] = 0,
vwActivityLogSummarySearchStats[AverageTimeTakenInMSPerSearch],
LOOKUPVALUE(
vwActivityLogSummarySearchStats[AverageTimeTakenInMSPerSearch],
vwActivityLogSummarySearchStats[Index],
vwActivityLogSummarySearchStats[Index]-1)
)

However on the dashboard if i select February 2016 vs February 2017 i get the difference values 

February 2016 : -8.08938476346708
February 2017 : 1.79808856418784

Showing the difference as 1.7 when this is incorrect, is there a way to get this to show the correct difference based on my selection.

Thanks

Tom

[v-ljerr-msft · Accepted solution]

Hi @TMAUND123,

If I understand you correctly, you should be able to use the formula below to create a new measure to Dynamically Calculating Difference between two rows based on filter selected. 

Difference =
VAR minIndex =
    MIN ( vwActivityLogSummarySearchStats[Index] )
VAR maxIndex =
    MAX ( vwActivityLogSummarySearchStats[Index] )
RETURN
    CALCULATE (
        SUM ( vwActivityLogSummarySearchStats[AverageTimeTakenInMSPerSearch] ),
        vwActivityLogSummarySearchStats[Index] = minIndex
    )
        - CALCULATE (
            SUM ( vwActivityLogSummarySearchStats[AverageTimeTakenInMSPerSearch] ),
            vwActivityLogSummarySearchStats[Index] = maxIndex
        )

Regards