Measure to 'build up' history...

[Matty]

Hi All,

Here's an example of my data:

Snapshot	Week	Sales and Forecast
2	1	10
2	2	10
2	3	10
2	4	10
3	2	11
3	3	8
3	4	7
4	3	9
4	4	5

Snapshot is the week the data was captured; green cells indicate sales (history) as at the snapshot; orange cells indicate forecast as at the snapshot.

I want a dax formula that will build up sales and forecast (for all weeks) based on the snapshot chosen. Below shows what the results would look like:

Snapshot Chosen	Sales and Forecast
2	40
3	36
4	35

In other words, if snapshot 3 was chosen, it'd be the sum of:

Week 1 sale (from snapshot 2)
+
Week 2 sale (from snapshot 3)
+
Weeks 3 and 4 forecast (from snapshot 3)

Also, if multiple snapshots were chosen, the dax measure should take the highest week, e.g. if snapshots 2 and 3 are chosen, the results from 3 should be returned.

Any help much appreciated. I will also continue to have a play.

Cheers,

Matty