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:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Projection[/TD]
[TD]Schedule[/TD]
[TD]Answer[/TD]
[TD]Formula in Column C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]=SUMPRODUCT(A$2:A2,N(OFFSET($B$2:$B2,ROWS($B$2:$B2)-ROW($B$2:$B2)+ROW($B$2)-1,0)))[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]90[/TD]
[TD]0[/TD]
[TD]90[/TD]
[TD]=SUMPRODUCT(A$2:A3,N(OFFSET($B$2:$B3,ROWS($B$2:$B3)-ROW($B$2:$B3)+ROW($B$2)-1,0)))[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]80[/TD]
[TD]2[/TD]
[TD]280[/TD]
[TD]=SUMPRODUCT(A$2:A4,N(OFFSET($B$2:$B4,ROWS($B$2:$B4)-ROW($B$2:$B4)+ROW($B$2)-1,0)))[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]70[/TD]
[TD]0[/TD]
[TD]250[/TD]
[TD]=SUMPRODUCT(A$2:A5,N(OFFSET($B$2:$B5,ROWS($B$2:$B5)-ROW($B$2:$B5)+ROW($B$2)-1,0)))[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]60[/TD]
[TD]0[/TD]
[TD]220[/TD]
[TD]=SUMPRODUCT(A$2:A6,N(OFFSET($B$2:$B6,ROWS($B$2:$B6)-ROW($B$2:$B6)+ROW($B$2)-1,0)))[/TD]
[/TR]
</tbody>[/TABLE]

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')