Excel DAX function add value above in column

Question

I'm trying to create a report that shows the current supply of incidents per week. I've got a table "Processing" with 5 columns:

"Year", "Week", "Created", "Closed" and "Supply"

The calculation I need to use is "Created"-"Closed" + "Supply" from the week before (the cell above). In a normal excel table this is easy enough but I can't figure this out with DAX.

I don't undestand if you want all the measure ((created-closed)+supply) a week before or only the Supply column of a week before. — nicolò grando, Feb 29 '16 at 15:10
Only the supply of the week before. The created and closed should be of this week — Ryflex, Mar 01 '16 at 22:13

score 0 · Accepted Answer · answered Mar 02 '16 at 13:06

To resolve your problem you have to create a index colum on your model. To create the index i create before a yearweek colum, with this calculate column formula:

   =[Year]*100+[week]

After that i have create a Index Column with this calculate column formula:

=CALCULATE(COUNT(Tabella1[week]), ALL(tabella1), FILTER(Tabella1;[Yearweek]<=EARLIER([Yearweek])))

the result is this on powerpivot:

after that cou create a calculate column, with this formula:

= [created]+[closed]+LOOKUPVALUE([supply];[Index];[Index]-1)

the result is this:

Excel DAX function add value above in column

1 Answers1