Forum Discussion

AndresSalomon's avatar
AndresSalomon
Helper II
8 years ago
Solved

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.-

Need Help
  • Rich_P's avatar
    Rich_P
    8 years ago

     

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    This calculated column formula will work

     

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

    Hope this helps.

5 Replies

  • Rich_P's avatar
    Rich_P
    Helper II
    8 years ago

     

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    This calculated column formula will work

     

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

    Hope this helps.

  • AndresSalomon's avatar
    AndresSalomon
    Helper II
    8 years ago

    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.-