cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

First non blank value

Hi Community, need your help to solve a problem. I think is prety simple but I'm not being able to solve it.

 

My scenario is the next one. I have this table.

Name ID___________Month_______Date
Name1___________Sep_________BLANK
Name1___________Oct_________BLANK
Name1___________Nov________11/01/2017
Name1___________Dec_________12/01/2017
...
Name2___________Sep_________BLANK
Name2___________Oct_________10/01/2017
Name2___________Nov_________11/03/2017
Name2___________Dec_________12/02/2017

I need to extract the first-non-blank date from the Date column for each Name ID, in a column. The final table should looks like:

Name ID________Month_______Date__________________First Date
Name1___________Sep_________BLANK_______________11/01/2017
Name1___________Oct_________BLANK_______________11/01/2017
Name1___________Nov________11/01/2017__________11/01/2017
Name1___________Dec_________12/01/2017__________11/01/2017
...
Name2___________Sep_________BLANK_______________10/01/2017
Name2___________Oct_________10/01/2017__________10/01/2017
Name2___________Nov_________11/03/2017_________10/01/2017
Name2___________Dec_________12/02/2017__________10/01/2017

I tried the FIRSTNONBLANK fuction inside CALCULATE, but it didn't work for me. Maybe I'm using it in the wrong way. 

 

Any advice would be helpful. Really thanks in advance.

 

Kind regards,

 

Andy.-

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Helper II
Helper II

FirstDateOfRange.jpg

 

Here is the DAX Formula I used.

FirstDateByName:=CALCULATE(FIRSTDATE(Table2[Date]),ALL(Table2[Month]),ALL(Table2[Date]))

There is probably a better way, but this works. Hope it heloped.

 

Rich

View solution in original post

Highlighted
Super User III
Super User III

Hi,

 

This calculated column formula will work

 

=CALCULATE(MIN([Date]),FILTER(Data,Data[Name ID]=EARLIER(Data[Name ID])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Highlighted
Helper II
Helper II

FirstDateOfRange.jpg

 

Here is the DAX Formula I used.

FirstDateByName:=CALCULATE(FIRSTDATE(Table2[Date]),ALL(Table2[Month]),ALL(Table2[Date]))

There is probably a better way, but this works. Hope it heloped.

 

Rich

View solution in original post

Highlighted
Super User III
Super User III

Hi,

 

This calculated column formula will work

 

=CALCULATE(MIN([Date]),FILTER(Data,Data[Name ID]=EARLIER(Data[Name ID])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Helper II
Helper II

Hey guys, really thaks for the help! I think both of your ideas can work but, FOR ME, I prefer to avoid using EARLIER fuction. It always brings me problems. 

 

Anyway both are great solutions. 

 

Thanks again. 

 

Kind regards,

 

Andy.-

Highlighted

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

Always happy to help (where I can). Gotta love this site and the members!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors