Forum Discussion
Find Difference Between Rows in Table based on Criteria
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% |
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])/_denominatorResult:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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)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])/_denominatorResult:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
