Help Needed Converting Excel Formula to DAX

[mbp257]

I would like to convert an excel formula into DAX (or find something that accomplishes the same goal) in order to save space and use pivot tables.

The formula references a single projection and multiples it by 'x' number of projects on a schedule at different points in time.

Example excel data:

	A	B	C	D
1	Projection	Schedule	Answer	Formula in Column C
2	100	1	100	=SUMPRODUCT(A$2:A2,N(OFFSET($B$2:$B2,ROWS($B$2:$B2)-ROW($B$2:$B2)+ROW($B$2)-1,0)))
3	90	0	90	=SUMPRODUCT(A$2:A3,N(OFFSET($B$2:$B3,ROWS($B$2:$B3)-ROW($B$2:$B3)+ROW($B$2)-1,0)))
4	80	2	280	=SUMPRODUCT(A$2:A4,N(OFFSET($B$2:$B4,ROWS($B$2:$B4)-ROW($B$2:$B4)+ROW($B$2)-1,0)))
5	70	0	250	=SUMPRODUCT(A$2:A5,N(OFFSET($B$2:$B5,ROWS($B$2:$B5)-ROW($B$2:$B5)+ROW($B$2)-1,0)))
6	60	0	220	=SUMPRODUCT(A$2:A6,N(OFFSET($B$2:$B6,ROWS($B$2:$B6)-ROW($B$2:$B6)+ROW($B$2)-1,0)))

<tbody>
</tbody>

Thanks

 

[mbp257]

Yes-all correct. I'm agnostic towards model architecture...just trying to see if the dynamic nature of this can be built into a measure.

Thanks again

 

[scottsen]

Something about the way the problem was phrased, or that I'm over worked or something, ... I dunno. I have a feeling I am making this way too complicated.

Regardless, take a look here: https://www.dropbox.com/s/69ksfvyifbcmqy4/Projections.xlsx?dl=0

It's a sample Excel 2013 model, that I think does what you want. The "answer" measure is just... not understandable by somebody that doesn't write this stuff daily

 

[mbp257]

Thanks. I was able to download the file but will need to open on my machine with '13 to view the measure(s).

Do you think a PowerPivot novice, such as myself, could easily modify this so that the user simply puts the number of projects starting in each period? (column J in 'RawData')