Pivot daily table to display previous monthend value
I have an requirement where I would like to populate each of the highlighted column below with the previous monthend value :
Wish to think that my set expression here is correct, but somehow the value in the 2nd column only populates on the row with REPORT_DT=31/3/2016, probably due to the "Max" within the set expression. Removing the "Max" just gives 0 in all rows.
Do advice on how to achieve this. I would like to populate the single value of 31/3/2016 in all the rows with April date and 29/2/2016 value for all the rows with March date and so forth.