Hello,
I am new to this forum but I was able to find lots of help from the active topics from here. I have an issue with my current dashboard which I hope someone knows how to solve here (if possible).
I have purchase data since 2021, to be updated daily into the future. These data contain all articles with the date of purchase and unit prices. I want to create a measure for a card that will show me % year change and % month change. For this, I want to multiply the latest unit price with total overall quantity per article (some purchase could be done only once, some happen each month or daily) and compare it to unit price * total quantity (same quantity as for latest UP) from year before, or the closest date to it and the same for the last month or closest date to it.
| Item nr. | Date | Purch. quantity | Unit price |
| 1 | 8.1.2021 | 10 | 497,48 |
| 1 | 7.6.2021 | 50 | 613,05 |
| 1 | 7.1.2022 | 100 | 613,05 |
| 2 | 7.1.2021 | 1 | 613,05 |
| 3 | 20.2.2021 | 5 | 472,35 |
| 3 | 7.3.2021 | 5 | 472,35 |
| 3 | 7.11.2021 | 5 | 1497,45 |
| 4 | 8.1.2021 | 10 | 487,43 |
| 4 | 8.1.2022 | 10 | 482,4 |
| 5 | 8.5.2021 | 100 | 402 |
| 6 | 8.1.2021 | 1 | 402 |
| 6 | 8.3.2021 | 1 | 402 |
| 6 | 8.6.2021 | 1 | 452,25 |
| 6 | 8.10.2021 | 1 | 804 |
This is an example of much longer data set, the expected result is something like this:
| Item nr. | Date | Purch. quantity | Unit price | Total Q * UP | Last value | Last years value |
| 1 | 8.1.2021 | 10 | 10 | 1600 | | 1600 |
| 1 | 7.6.2021 | 50 | 12 | 1920 | | |
| 1 | 7.1.2022 | 100 | 15 | 2400 | 2400 | |
| 2 | 7.1.2021 | 1 | 50 | 50 | 50 | 50 |
| 3 | 20.2.2021 | 5 | 5 | 75 | | 75 |
| 3 | 7.3.2021 | 5 | 5 | 75 | | |
| 3 | 7.11.2021 | 5 | 8 | 120 | 120 | |
| 4 | 8.1.2021 | 10 | 15 | 300 | | 300 |
| 4 | 8.1.2022 | 10 | 20 | 400 | 400 | |
| 5 | 8.5.2021 | 100 | 5 | 500 | 500 | 500 |
| 6 | 8.1.2021 | 1 | 20 | 80 | | 80 |
| 6 | 8.3.2021 | 1 | 25 | 100 | | |
| 6 | 8.6.2021 | 1 | 25 | 100 | | |
| 6 | 8.10.2021 | 1 | 30 | 120 | 120 | |
Then the measure should just do
Current/first % = (Last value-Last years value)/Last years value
(3590-2605)/2605= 38%
Any help would be much appreciated!
