Measure to 'build up' history...

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hi All,

Here's an example of my data:

SnapshotWeekSales and Forecast
2110
2210
2310
2410
3211
338
347
439
445


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 ChosenSales and Forecast
240
336
435


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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top