I think this is a simple question, I’m just not sure how to go about doing it. In PowerBI, I have the below as an example, which are two time series’:
| Date | Series1 | Series2 |
|---|---|---|
| 1-May | 0.650239 | 0.994032 |
| 2-May | 0.689518 | 0.457408 |
| 3-May | 0.969402 | 0.863187 |
| 4-May | 0.812362 | 0.547719 |
| 5-May | 0.760445 | 0.717409 |
| 6-May | 0.342214 | 0.091408 |
| 7-May | 0.985581 | 0.066404 |
| 8-May | 0.236865 | 0.507268 |
| 9-May | 0.744359 | 0.803286 |
| 10-May | 0.470509 | 0.221594 |
| 11-May | 0.487833 | 0.648442 |
| 12-May | 0.541049 | 0.143225 |
| 13-May | 0.041194 | 0.335399 |
| 14-May | 0.191586 | 0.951685 |
As I’ve tried to demonstrate, both of the series’ are in the same table. All I’m trying to do is create a separate table which includes the most recent value and average for each series, something like the below:
| Latest | Average | |
|---|---|---|
| Series1 | 0.191586 | 0.56594 |
| Series2 | 0.951685 | 0.52489 |
I tried pivoting and unpivoting in PowerQuery, but can’t seem to put it in the right format. Can someone please advise on how to go about creating the separate table? I was originally thinking I’d have to create a separate measure for each calculation, but even then couldn’t figure out how to format according to what I’m trying to achieve. Thanks very much.