cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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: 

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

1 REPLY 1
Super User III
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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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