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

 

[scottsen]

Sorry, I am too lame at "normal" excel to help you translate this. I tried to intuit the meaning by looking at the "Answer" column, but totally failed.

Can you maybe expand out a few "Answers". Like 250 comes from... 90+70+60 (all the schedule 0)... that clearly isn't it...

 

[mbp257]

What this amounts to is several projects at different stages of their life cycle.

In period 1, 1 project starts.
In period 2, 0 projects start.
In period 3, 2 projects start.
And so on...

It probably would've been clearer if I added a hypothetical date next to the schedule.

This is essentially what the formula is doing:

Period 1	Period 3	Period 3	Answer
Project 1	Project 1	Project 2	(Sum)
100	–	–	100
90	–	–	90
80	100	100	280
70	90	90	250
60	80	80	220

<tbody>
</tbody>

 

[scottsen]

I think I am slowly wrapping my head around what you are saying? In the initial table, "Schedule" is more like "NumProjectsStarting" ? A row kind of represents a "period of time", but also... Some sort of "projection" (from period 2 to 3, the projection changes from 90 to 80?)

 

[mbp257]

You're exactly right re: "Schedule" and "period of time".

The "Projection" is a static set of inputs representing a typical amount to be produced each month. These inputs are at Time = 0 and are not calendar specific but represent the amount that will be produced in each subsequent month after the project starts. So, when NumProjectsStarting for November = 1, one project starts at the beginning of the "Projection". In December, if NumProjectsStarting = 2, 2 projects start at the beginning of the projection while November's project is in the second month of the "Projection".

Thanks

 

[olivierhbh]

Hi,

Same as you Scott, I couldn't understand how the answers were calculated. But now I do, and I tried to create columns/measures to replicate this formula but without any success.

Mbp, do you have to keep that exact base structure? I mean only 3 columns: period, projection, schedule. How flexible are you?

Olivier.

 

[mbp257]

Base structure is flexible. Totally amenable to helper columns, etc.

 

[scottsen]

Ya, I am gonna need to simplify this or something

If there was a table like...

MonthOffset Projection
0 100
1 90
2 80

That is totally separate from other data... does that "make sense" in your scenario? (All projects have the same projections?)

 

[mbp257]

Separate tables work fine. In fact, I will end up having several Projection tables with corresponding Schedule tables.

• factProjectionType1
• factProjectionType2
• factScheduleType1
• factScheduleType2

Thanks

 

[scottsen]

Just for the record, you will likely have an easier time if you do a single Project table (with a Type column) and a separate Schedule table (again with a type column). Dealing with "parallel tables" is non-fun.

So, let me see if I have this right... We have a projected # of units (per period offset), and some # of projects coming online (I assume "by date"?).

The 280 in C3 of your original table is "2 projects at 1st period + 1 project at 3rd period = 100*2 + 80."