Latest/Max Date across Columns some with blank dates

[Anonymous]

Hi PBI gurus, 

I need help to pull out the latest/most recent date from 6 different columns of client appointment dates. The goal is to be able to plot clients first appointment scores, against their most recent scores, on a radar chart. 

I've tried using Statistics > Max and Date > Latest in the query editor, but because some clients have e.g. only had 1 appointment it's throwing up an error because of the blanks in the subsequent columns. 

Data looks like this: 

UserID   FirstAppt[Date]  FirstAppt[Score]   SecondAppt[Date]  SecondAppt[Score]  ThirdAppt[Date] ThirdAppt[Score]      

1654      16/4/2019          7.6                       22/6/2019                7.9                            4/11/2019           8.2

1978      12/1/2018          5.2                       

2043      30/9/2018          6.8                       15/10/2018              6.4                       

Do I need to wrap a series of IF(ISBLANK()   statements around each column or is there a cleaner way I can do this? 

Thankyou 🙂

Shiv

[dax]

Hi shivvy,

You could try to use 0 to replace null in score and use 1900/1/1 to replace null in date, then try to find the max date and score in M code to see whether it work or not.

= Table.ReplaceValue(#"Added Custom",each [amount],each if [amount]=null then 0 else [amount],Replacer.ReplaceValue,{"amount"})

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Try union and summarize

https://docs.microsoft.com/en-us/dax/summarize-function-dax

Group all dates into one column and then take max, able on that id

example

Summary = UNION(
SUMMARIZE('Table1','Table1'[Customer],'Table1'[created_date],'Table1'[DA Status],"Sales",sum('Table1'[Sales]),"Invoices",count('Table1'[ID]),"Time",DIVIDE(SUM('Table1'[Ship Time]),1),"Stage"," Ship","Gross",sum('Table1'[inv_amount]),"Net",Sum('Table1'[Net Calc Amount]))
,SUMMARIZE('Table2','Table2'[Customer],'Table1'[created_date],'Table2'[AA Status],"Sales",sum('Table2'[Sales]),"Invoices",count('Table2'[ID]),"Time",DIVIDE(SUM('Table1'[Order Time]),1),"Stage","Order","Gross",sum('Table1'[inv_amount]),"Net",Sum('Table1'[Net Calc Amount]))
)