Good afternoon: I need to find a way to calculate the numerical differences between rows for specific categories in Google Data Studio, for a dataset that is formatted as a database, with dates, categories and numerical measures.
I have my data in a "unpivoted" table with date, category and value of this type: unpivot data
| Fecha | Activo | Precio |
|---|---|---|
| 3/01/2020 | AAPL | 572,00 |
| 3/01/2020 | NFLX | 1.571,50 |
| 3/01/2020 | NOKA | 293,00 |
| 3/01/2020 | TECO2 | 163,45 |
| 6/01/2020 | AAPL | 582,50 |
| 6/01/2020 | NFLX | 1.650,00 |
| 6/01/2020 | NOKA | 304,50 |
| 6/01/2020 | TECO2 | 167,70 |
| 7/01/2020 | AAPL | 581,38 |
| 7/01/2020 | NFLX | 1.645,00 |
| 7/01/2020 | NOKA | 310,00 |
| 7/01/2020 | TECO2 | 168,15 |
| 8/01/2020 | AAPL | 587,25 |
| 8/01/2020 | NFLX | 1.650,00 |
| 8/01/2020 | NOKA | 316,50 |
| 8/01/2020 | TECO2 | 170,35 |
| 9/01/2020 | AAPL | 603,38 |
| 9/01/2020 | NFLX | 1.658,00 |
| 9/01/2020 | NOKA | 318,00 |
| 9/01/2020 | TECO2 | 171,80 |
That is to say: there are several time series in database format with records.
I need to calculate the daily changes for each category, that is: for each category, the difference with the previous day. In this way: unpivot data with daily difference for each category
| Fecha | Activo | Precio | |
|---|---|---|---|
| 3/01/2020 | AAPL | 572,00 | 0 |
| 3/01/2020 | NFLX | 1.571,50 | 0 |
| 3/01/2020 | NOKA | 293,00 | 0 |
| 3/01/2020 | TECO2 | 163,45 | 0 |
| 6/01/2020 | AAPL | 582,50 | 10,50 |
| 6/01/2020 | NFLX | 1.650,00 | 78,50 |
| 6/01/2020 | NOKA | 304,50 | 11,50 |
| 6/01/2020 | TECO2 | 167,70 | 4,25 |
| 7/01/2020 | AAPL | 581,38 | -1,12 |
| 7/01/2020 | NFLX | 1.645,00 | -5,00 |
| 7/01/2020 | NOKA | 310,00 | 5,50 |
| 7/01/2020 | TECO2 | 168,15 | 0,45 |
| 8/01/2020 | AAPL | 587,25 | 5,87 |
| 8/01/2020 | NFLX | 1.650,00 | 5,00 |
| 8/01/2020 | NOKA | 316,50 | 6,50 |
| 8/01/2020 | TECO2 | 170,35 | 2,20 |
| 9/01/2020 | AAPL | 603,38 | 16,13 |
| 9/01/2020 | NFLX | 1.658,00 | 8,00 |
| 9/01/2020 | NOKA | 318,00 | 1,50 |
| 9/01/2020 | TECO2 | 171,80 | 1,45 |
I need to get the daily differences for each category in the last 10 or 20 days. Whether it's in a simple table, card, or graph, it doesn't matter, I just need to get those measurements, like the ones in the fourth column.
Needless to say, this is easy to do in a spreadsheet, or with Python or R. Even in Power BI it can be done with DAX. But I need to do it in Google Data Studio.
I take the data from Google Sheets directly. Here is the original dataset: https://docs.google.com/spreadsheets/d/1VBjpx0I6RcqaPPNkwxxNukkRIqtzZmgobmyV6OoJCVo/edit?usp=sharing
I've tried calculated fields, measures, and functions, but I can't get anywhere near the solution. I don't know the formulas to do operations between rows. That's why I think maybe it would be better to pivot the table with one column for each stock; although I think it would be more correct to use the raw dataset and try to do it in Data Studio, since I need to learn how to use it because my current job prefers this to Power BI and Tableau.
That's why I would like to know if there is any way to do it within Google Data Studio
Thank you very much to all.