cancel
Showing results for
Did you mean:
New Member

## previous week value

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:

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors