You might not have transactions of every customer for everyday.
Suppose I opened my account on 1st Jan 2017 with balance 1000.
Then I added 500 on 15th Jan, 500 on 1st Feb, 500 on 15th Feb and 500 on 1st March.
Your Final fact table will have 5 rows for my data. (By your method it would have been 65)
*Last Valid to is today's date.
Now You have to Link Key with Calendar with an intermediate Table that contains all dates valid from and valid for a key.
Use interval match to make such table.
I have created the sample data in attached excel.
This should reduce your application size significantly.
Sample Data.xlsx 10.0 K
I had a pretty similar issue than yours; I will show you how I handled it and I hope that would help you :
Here is what I have :
this is my 'customer table', each custmer (id) has a situation (A or B), in a certain date (you can have only one date and create a period in the script to use it in the intervalMatch later):
this is my 'Fact' table:
Now, the point is to track the sales of each client and see their state (if they were actif or blocked (A or B)
But if a client (ID 1) was actif like this :
10/01/2017 10/01/2017 A 11/01/2017 11/01/2017 A 12/01/2017 12/01/2017 A 13/01/2017 13/01/2017 A 14/01/2017 14/01/2017 B
I would rather have a table like this :
client | |period |situation
1 | 10/01/2017 13/01/2017 |A
1 | 14/01/2017 14/01/2017 |B
Which will significally reduce the size of my table
And then , I will match the sales dates of the fact table with these periods to see why my clients are not having any sales; (in fact they were blocked)
Hope the situation is rather clear, so you can alter my script to you need:
Here is the script:
(ooxml, embedded labels, table is Feuil2);
(ooxml, embedded labels, table is Feuil1);
If(id = Previous(id),
If(situation = Previous(situation), Peek('Flag'), RangeSum(Peek('Flag'), 1)), 1) as Flag
Order By id, debut;
DATE(MinDate)&' - '&date(MaxDate) as Period;
Date(Min(debut)) as MinDate,
Date(Max(fin)) as MaxDate
Group By id, situation, reclamation,Flag;
DROP Tables Table, TempTable;
inner join IntervalMatch("date",id)
MaxDate,id Resident FinalTable;
The result :
Hope this helps,