Forum Discussion
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 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.
11 Replies
- prateekraina
Memorable Member
Hi Anonymous,
Can you please elaborate on how are you going to use those values?
Simply getting the value doesn't make sense.
Regards,Prateek Raina
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).
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.
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
- Greg_Deckler
Community Champion
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],",")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
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.
