Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Newbie here, I'm trying to calculate columns that show the progression of my sales for an especific date.
Here an example of my data:
Snapshot | Date | Quantity | Amount |
01/07/2018 | 16/08/2018 | 5 | 600 |
02/07/2018 | 16/08/2018 | 6 | 720 |
03/07/2018 | 16/08/2018 | 7 | 840 |
04/07/2018 | 16/08/2018 | 8 | 960 |
05/07/2018 | 16/08/2018 | 9 | 1080 |
06/07/2018 | 16/08/2018 | 8 | 960 |
07/07/2018 | 16/08/2018 | 8 | 960 |
08/07/2018 | 16/08/2018 | 8 | 960 |
09/07/2018 | 16/08/2018 | 10 | 1200 |
10/07/2018 | 16/08/2018 | 11 | 1320 |
11/07/2018 | 16/08/2018 | 12 | 1440 |
12/07/2018 | 16/08/2018 | 13 | 1560 |
13/07/2018 | 16/08/2018 | 14 | 1680 |
14/07/2018 | 16/08/2018 | 15 | 1800 |
15/07/2018 | 16/08/2018 | 20 | 2400 |
16/07/2018 | 16/08/2018 | 25 | 3000 |
01/07/2018 | 17/08/2018 | 4 | 480 |
02/07/2018 | 17/08/2018 | 5 | 600 |
03/07/2018 | 17/08/2018 | 6 | 720 |
04/07/2018 | 17/08/2018 | 7 | 840 |
05/07/2018 | 17/08/2018 | 8 | 960 |
06/07/2018 | 17/08/2018 | 7 | 840 |
I want to achieve something like this:
Today 10/8/18 | 30 days ago | n days ago | ||||
Day | Quantity | Amount | Quantity | Amount | Quantity | Amount |
16/08/2018 | 25 | 3000 | 11 | 1320 | … | … |
17/08/2018 | 20 | 2400 | 10 | 1200 | … | … |
18/08/2018 | 17 | 2040 | 10 | 1200 | … | … |
The first two quantity/amount columns represent today, the second ones 30 day ago, the third ones "n" days ago just as an example.
So this way I can see the progression for an especific date.
Thanks in advande for your help!
Hi @dljdavid,
Could you please explain the values in your desired result? What is the calculation rule? For example, does the first column represent continual dates? Why the quantity in first row under Total (Today 10/8/18) is 25? Please provide more description to help us understand your requirement.
Regards,
Yuliana Gu
Dear @v-yulgu-msft,
Thank you very much for your reply!
Yes, the first column represent continual dates, I have redone my example to show that:
So the first column would represent the whole month of August.
Imagine that today is 10-08-2018
The quantities represent how much I have sold for that date (first column) depending on a snapshot date. So for the 01/08/2018 I have sold a quantity of 35 and an amont of 4200 as today 10-08-2018.
To achieve this I have a snapshot of every day in the future, so as you can see in my data example, from 01/07/2018 I have an snapshot that goes up to 10-08-2018.
The 30 days ago columns (11-07-2018) shows how much I had sold according the snapshot date, so for 01-08-2018 I had on the books a quantity of 20 and an amount of 2400.
Hope is this helps, if not let me know your further questions, and thank you for looking into this!
David
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |