Forum Discussion

Anonymous

Not applicable

9 years ago

Solved

Get Last 3 Non Blank values from a Column

Hello All, I have table as follows. Here i need to get the values of 5/31/2017, 5/30/2017 and 5/29/2017 values from comments columns if my table contains comments column as I.e ...

Anonymous
9 years ago
I think i got the solution.
I did mentioned in Top N filtering of visual level filter of fields.
And it worked for me.

Anonymous

Not applicable

9 years ago

Thanks for the reply Greg_Deckler

I did tried what you have suggested.

i didnt get any errors but i didnt get any output also.

Greg_Deckler

Community Champion

9 years ago

Hmm, what data set are you using with that? Looks OK formula-wise. I did not try the use case where there are no blanks.

Anonymous
Not applicable
9 years ago
Greg_Deckler please have a look at the below reply.
I have given info about the data that i am working.
- v-qiuyu-msft
  Community Support
  9 years ago
  Hi Anonymous,
  
  You can also create a index column in Power Query with code below:
  
  Then create a measure:
  
  Last3Blanks = var l= CALCULATE(MAX('Calendar'[Index]),FILTER(ALL('Calendar'),'Calendar'[Task]=MAX('Calendar'[Task]) &&'Calendar'[Comment]=BLANK() ))
  return
  CONCATENATEX(CALCULATETABLE('Calendar',FILTER('Calendar','Calendar'[Index]<l && 'Calendar'[Index]>=l-3)),'Calendar'[Date],",")
  
  You can download attached .pbix file.
  
  Best Regards,
  Qiuyun Yu
  Get Last 3 Non Blank values from a Column.zip57 KB
  - prateekraina
    Memorable Member
    9 years ago
    Hi v-qiuyu-msft,
    
    Out of curiosity, how did you attach the zip file in a reply? :D
    
    Regards,
    Prateek Raina