Moving values of a cumulative measure (which also has nulls) down by two rows using DAX

[mathangi129]

Hello all,

I have a cumulative sum measure (which also has nulls and zeros). I want to move this value down by two rows, which means that first two rows will always be empty or null. The NULL values should also be moved down accordingly. I am developing this Tabular cube in Visual studio and deploying and testing in SSMS.

Eg. :  

Week    |Cumulative Sum | Move by two rows

Week 0 | NULL  |  NULL

Week 1 | NULL  |  NULL

Week 2 |  1        |  NULL

Week 3 |  0        |  NULL

Week 4 | NULL  |  1

Week 5 | 2         |  0

Week 6 | 0         |  NULL

Week 7 | NULL  |  2

Can someone suggest DAX measure formula to implement this?

I already tried CALCULATE(Cumulative Sum;FILTER(ALLSELECTED(Table);Weekid <=MAX(Weekid)-2)

But this does not work. Please help me solve this problem. 

Thanks in advance for your time and help! 

[v-ljerr-msft]

Hi @mathangi129,

Is the data in the same table or in different tables?

Could you post your table structures(including the relationships) with some sample/mock data, so that we can better assist on this issue?

It's better to share a dummy pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards