previous week value

[vincent1618]

Hi everyone, can somebody help me out with this challenge I'm facing. 

Here is my sample data:

ID     Week      Var1   

1           1            no

2           1            null

3           1            yes

1           2            yes

2           2            yes

3           2            yes

So I have a unique ID, weeknumber and a variable (yes, no, empty). My goal is to track the changes from 0 -> 1 every week (so not from empty (null) -> 1).

My idea was to add a column with the value of the previous week for that unique ID. But somehow it doesn't work. This is what I've tried: 

previous = CALCULATE(sum(Blad1[Var1]);Blad1[ID];Blad1[week]-1) 

and also tried this solution, but nothing: 

https://stackoverflow.com/questions/40120580/power-bi-dax-calculating-last-week-sales-for-all-the-fi...

Does someone has a solution for this problem? Would really appreciate it!

[Ashish_Mathur · Accepted solution]

Hi,

Enter this calculated column formula

=CALCULATE(LASTNONBLANK(Data[Var1],1),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Week]<EARLIER(Data[Week])))

Hope this helps.