Forum Discussion

Helper I

6 years ago

Solved

Difference from previous cell

Hello,

I want to make a custom column that calculates the difference from the previous cell.

The testedPositive is not the increasing number of people on that day, but the cumulative total up to that day.

What I want to get is the increased number of people in each city from the day before.

That's the Difference column in the table below.

I know it's not a DAX-friendly process, but please help.

Greg_Deckler
6 years ago
Unless it is prefecture columns throwing things off I would think:

diff = [testedPositive] - maxx(filter('prefectures4',[prefectureNameE]=earlier([prefectureNameE]) && [Last date] <earlier([Last date])),[testedPositive])

4 Replies

amitchandak
Super User
6 years ago
owlet0214 , prefer to create a date from the year , month and day and create two new columns like

Last date = maxx(filter(table,[city]=earlier([city]) && [Date] <earlier([Date])),[Date])
diff = [tested positive] - maxx(filter(table,[city]=earlier([city]) && [Date] <earlier([Last Date])),[tested positive])
- owlet0214
  Helper I
  6 years ago
  Thanks a lot !
  
  I haven't gotten the expected number yet, but I think it's getting closer to the correct answer.
  I will try a little more.
  
  I use below.
  
  Last date = maxx(filter('prefectures4',[prefectureNameE]=earlier([prefectureNameE]) && [YYYYMMDD2] <earlier([YYYYMMDD2])),[YYYYMMDD2])
  
  diff = [testedPositive] - maxx(filter('prefectures4',[prefectureNameE]=earlier([prefectureNameE]) && [YYYYMMDD2] <earlier([Last date])),[testedPositive])
  - Greg_Deckler
    Community Champion
    6 years ago
    Unless it is prefecture columns throwing things off I would think:
    
    diff = [testedPositive] - maxx(filter('prefectures4',[prefectureNameE]=earlier([prefectureNameE]) && [Last date] <earlier([Last date])),[testedPositive])