Sql code sales value last day of every month

Hi guys

hope you are all well

I am struggling to write a sql code which will pick up the sales value of the last date of each month.

I have this below example table showing sales which accumulate daily for each month, the late day of the month is the final sales value ( this is just example table in reality there are much more months)

from the table I need to write a query ( select query etc) that will show the below

The past sales is the sales number of the last day of the previous month ( for this case we start at November so the past sales for November is blank, for December past sales is the value you see for 31/11/2020 ) current sales is the last day of the month in question.

Can this be done? can anyone help me do this?

thank you

hi hope this helps

drop create sample data .. click arrow to the left
drop table #SampleData 

create table #SampleData 
(
[Date] date , Sales int 
)

set dateformat  dmy 
insert into #SampleData values 
  ('10/11/2020',30) 
, ('21/11/2020',60) 
, ('30/11/2020',75) 
, ('20/12/2020',110) 
, ('31/12/2020',130) 
, ('27/01/2021',140) 
, ('28/01/2021',195) 
, ('29/01/2021',250)
; with cte as 
(
   select 
            convert(varchar(7), [Date], 126) as date
		 ,  max(Sales) as currentsales 
   from 
        #SampleData  
  group by 
       convert(varchar(7), [Date], 126)
)
select [date],lag(currentsales) over(order by [date]) pastsales,currentsales  from cte 

image

Thank you