cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MohanV Established Member
Established Member

Get Last 3 Non Blank values from a Column

Hello All,

I have table as follows.

1.PNG

 

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

 

2.PNG

I.e the last value is not available then it should get the values of last three dates values i.e 31, 30, 29.

 

Any suggestions.

Mohan V.

1 ACCEPTED SOLUTION

Accepted Solutions
MohanV Established Member
Established Member

Re: Get Last 3 Non Blank values from a Column

I think i got the solution.

I did mentioned in Top N filtering of visual level filter of fields.

enter image description here

And it worked for me.

11 REPLIES 11
prateekraina New Contributor
New Contributor

Re: Get Last 3 Non Blank values from a Column

Hi @MohanV,

 

Can you please elaborate on how are you going to use those values?
Simply getting the value doesn't make sense.

Regards,

Prateek Raina

Super User
Super User

Re: Get Last 3 Non Blank values from a Column

Will have to do some work on this, but how about something like this?

 

Last3Blanks = var LastBlank = CALCULATE(MAX('Calendar'[Date]),FILTER('Calendar','Calendar'[Comments]=""))
RETURN CONCATENATEX(CALCULATETABLE(VALUES('Calendar'[Date]),FILTER('Calendar','Calendar'[Date]<=LastBlank && 'Calendar'[Date] >= LastBlank-2)),'Calendar'[Date],",")

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


MohanV Established Member
Established Member

Re: Get Last 3 Non Blank values from a Column

Thanks for the reply @prateekraina & @Greg_Deckler

 

Actually i have a table where i get the status of the emplyees task day by day.

As in below image i get the values(Confidential, so just enterd dumy data).

3.PNG

 

 

Then i have unpivoted the dates columns and made a copy of that column and to that column splitted with space so that i can get the date value.

4.PNG

 

there will be number of tasks and i get this kind of data everyday.

Here i need to show the last 3 commneted values in a table visualization for each task.

Like as i mentioned above if the 5/31/2017 value is not available then it should get the values of 5/29/2017 plan, 5/29/2017 actual, 5/30/2017 plan values i.e nothing but the last 3 non blank values.

 

If you need any furthur info please feel free to ask.

 

Mohan.V

 

 

 

If i need to get the la

MohanV Established Member
Established Member

Re: Get Last 3 Non Blank values from a Column

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.

5.PNG

 

Super User
Super User

Re: Get Last 3 Non Blank values from a Column

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.

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


MohanV Established Member
Established Member

Re: Get Last 3 Non Blank values from a Column

@Greg_Deckler please have a look at the below reply.

I have given info about the data that i am working.

MohanV Established Member
Established Member

Re: Get Last 3 Non Blank values from a Column

I think i got the solution.

I did mentioned in Top N filtering of visual level filter of fields.

enter image description here

And it worked for me.

Moderator v-qiuyu-msft
Moderator

Re: Get Last 3 Non Blank values from a Column

Hi @MohanV,

 

You can also create a index column in Power Query with code below:

 

e1.PNG

 

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],",")

 

e2.PNG

 

You can download attached .pbix file.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
prateekraina New Contributor
New Contributor

Re: Get Last 3 Non Blank values from a Column

Hi @v-qiuyu-msft,

 

Out of curiosity, how did you attach the zip file in a reply? Smiley Very Happy

Regards,

Prateek Raina