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_______DateName1___________Sep_________BLANKName1___________Oct_________BLANKName1___________Nov________11/01/2017Name1___________Dec_________12/01/2017...Name2___________Sep_________BLANKName2___________Oct_________10/01/2017Name2___________Nov_________11/03/2017Name2___________Dec_________12/02/2017I 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 DateName1___________Sep_________BLANK_______________11/01/2017Name1___________Oct_________BLANK_______________11/01/2017Name1___________Nov________11/01/2017__________11/01/2017Name1___________Dec_________12/01/2017__________11/01/2017...Name2___________Sep_________BLANK_______________10/01/2017Name2___________Oct_________10/01/2017__________10/01/2017Name2___________Nov_________11/03/2017_________10/01/2017Name2___________Dec_________12/02/2017__________10/01/2017I 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.
Go to Solution.
Here is the DAX Formula I used.
There is probably a better way, but this works. Hope it heloped.
View solution in original post
This calculated column formula will work
=CALCULATE(MIN([Date]),FILTER(Data,Data[Name ID]=EARLIER(Data[Name ID])))
Hope this helps.
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.
Always happy to help (where I can). Gotta love this site and the members!
Check out the on demand sessions that are available now!
Check out the Winners!
Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.