Using variables to get previous values


#1

This has been driving me a bit crazy all day - and on the face of it should be a simple thing to achieve but I simply cannot get my head around it

To set the scene, I am working using Direct Query and therefore have various restrictions in the way I can try to implement this. What I need is to use a previously calculated measure and figure out the movement made in that measure from one month to the next

My date table is a period table with a single row per month (first day) and the PTR calculation uses the work % complete to figure out what profit can be taken in a given period

You can see from the below table that currently my previous PTR measure matches the current PTR
PTR%20Table

This is the measure code for the previous PTR measure
PTR%20Previous

I am trying to set a variable that fixes the Calculated PTR and then returns the previous value so that I can then compare the two values and simply calculate the change

I just cannot figure it out - yet it seems like something that should be so simple


#2

Does this technique answer this perfectly for you

I think it should.

Give it a go.

Chrs
Sam


#3

Hi Sam

Thanks for the pointer

This method got me to the required result (eventually)

I realised that as my Calculated PTR measure was actually a result of multiple branched measures it was actually the root level measures that I needed to apply this this technique to.

Therefore I had to create “previous” measures against each of my underlying measures to monitor the prior values that could then be summed up at the top project level. Seems very messy…but it works… FINALLY

Thanks again - really appreciate it

Dave


#4

Brilliant, that’s great.

Chrs