Hi,
I have store sales data by reporting week, but not each reporting week has data for each store. What I want to do is create a new table by reporting week that sums the sales data of the latest entry of each store in order to create a line graph by reporting week.
| ROW | STORE_KEY | Reporting Week (a) | Value | This entry rank by store |
| 1 | A | 201801 | 342.99 | 1 |
| 2 | A | 201802 | 2,608.26 | 2 |
| 3 | B | 201802 | 2,771.60 | 1 |
| 4 | B | 201804 | 10,613.88 | 2 |
| 5 | A | 201805 | 852.88 | 3 |
| Reporting Week (b) | Value | Calculation explantion 1 | Calculation explantion 2 | Calculation explantion 3 |
| 201801 | 342.99 | Row 1 | 342.99 + null | only store 2497 has entry before or equal to week 201801 |
| 201802 | 5,379.86 | Row 2 + Row 3 | 2,608.26 + 2,771.60 | Row 1 data from 201801 is replaced by Row 2 data from 201802 |
| 201803 | 5379.86 | Row 2 + Row 3 | 2,608.26 + 2,771.60 | No new data from 201803 so latest data from 201802 is used |
| 201804 | 13,222.14 | Row 2 + row 4 | 2,608.26 + 10,613.88 | , Store A's latest data is from 201802 so this is used, Store B has data from 201804 which is used |
| 201805 | 11,466.76 | Row 4 + Row 5 | 10,613.88 + 852.88 | Store A has data from 201805 so this is used, Store B's latest data is still from 201804 |
I've been succesfull in summing the sales value if Reporting week (a) from table 1 is LT or EQ to Reporting week (b) from my calculated table but this sums all values whereas I only want to sum the latest value for each STORE KEY. I've tried using measures using MAX functions of the entry rank but have been unsuccesful. help please!
