Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mathangi129
Regular Visitor

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

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! 

1 REPLY 1
v-ljerr-msft
Employee
Employee

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 uploadingSmiley Happy

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.