Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dljdavid
Frequent Visitor

Calculate data according to an snapshot

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:

 

SnapshotDateQuantityAmount
01/07/201816/08/20185600
02/07/201816/08/20186720
03/07/201816/08/20187840
04/07/201816/08/20188960
05/07/201816/08/201891080
06/07/201816/08/20188960
07/07/201816/08/20188960
08/07/201816/08/20188960
09/07/201816/08/2018101200
10/07/201816/08/2018111320
11/07/201816/08/2018121440
12/07/201816/08/2018131560
13/07/201816/08/2018141680
14/07/201816/08/2018151800
15/07/201816/08/2018202400
16/07/201816/08/2018253000
01/07/201817/08/20184480
02/07/201817/08/20185600
03/07/201817/08/20186720
04/07/201817/08/20187840
05/07/201817/08/20188960
06/07/201817/08/20187840

 

I want to achieve something like this:

 

 Today 10/8/1830 days agon days ago
DayQuantityAmountQuantityAmountQuantityAmount
16/08/2018253000111320
17/08/2018202400101200
18/08/2018172040101200

 

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!

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

 

Captura.JPG

 

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.