Find Difference Between Rows in Table based on Criteria

[Anonymous]

I am trying to calculate the percent change compared to the data for 3/15/20, for each date for each subvertical. Here is an example of the data. The last column is what I am trying to calculate. So for the first row, I need the formula to be 981,000/987,000. Can anyone help me?

Sub Vertical	Date	Total Punches	Difference from 3/15
Transporation	3/8/2020	981,000	99%
Transporation	3/15/2020	987,000	100%
Transporation	3/22/2020	890,000	90%
Transporation	3/29/2020	850,000	86%
Transporation	4/5/2020	650,000	66%
Hotels	3/15/2020	675,000	100%
Hotels	3/22/2020	644,000	95%

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a measure as below.

Difference from 3/15 = 
var _denominator = 
CALCULATE(
    SUM('Table'[Total Punches]),
    FILTER(
        ALL('Table'),
        'Table'[Sub Vertical] = SELECTEDVALUE('Table'[Sub Vertical])&&
        'Table'[Date] = DATE(2020,3,15)
    )
)
return
SELECTEDVALUE('Table'[Total Punches])/_denominator

Result:

Best Regards

Allan

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

[lbendlin]

try this one:

Difference = 
var s = SELECTEDVALUE(Table[Sub Vertical])
var tp315 = CALCULATE(sum(Table[Total Punches]), Table[Sub Vertical]=s, Table[Date]=date(2020,3,15))
return divide(sum(Table[Total Punches]),tp315)