Q:

SQL Previous Record Query using multiple columns

I am trying to create a table that has both the current value in and the previous months value using an SQL query in Power BI. I'm querying a Redshift database through ODBC. However I cannot get the result I am expecting...
The code below shows the current idea (I have tried many tweaks on this but this is the closest I can get).
select

entity, DateAdd(mm,-1,CAST('01-' + "submission period" AS Date)) AS "submission period", value, DateAdd(mm,-2,CAST('01-' + "submission period" AS Date)) as previous_period,

(
    select TOP 1 value
    from 
    table1 b2
    where DateAdd(mm,-2,CAST('01-' + b2."submission period" AS Date)) = CAST('01-' + b1."submission period" as Date) 
    AND b2.entity = b1.entity

) as previous_period_value

from table1 as b1

The table is this:
+--------+-------------------+-------+
| entity | submission period | value |
+--------+-------------------+-------+
| ABC1   | Jan-19            |     2 |
| ABC2   | Jan-19            |     3 |
| ABC3   | Jan-19            |     4 |
| ABC1   | Feb-19            |     8 |
| ABC2   | Feb-19            |    11 |
| ABC3   | Feb-19            |    11 |
| ABC1   | Mar-19            |     6 |
| ABC2   | Mar-19            |    43 |
| ABC3   | Mar-19            |     6 |
| ABC1   | Apr-19            |    73 |
| ABC2   | Apr-19            |     2 |
| ABC3   | Apr-19            |     6 |
+--------+-------------------+-------+

Expected result:

+--------+-------------------+-----------------+-------+-----------------------+
| entity | submission period | previous_period | value | previous_period_value |
+--------+-------------------+-----------------+-------+-----------------------+
| ABC1   | Jan-19            | null            |     2 | null                  |
| ABC2   | Jan-19            | null            |     3 | null                  |
| ABC3   | Jan-19            | null            |     4 | null                  |
| ABC1   | Feb-19            | Jan-19          |     8 | 2                     |
| ABC2   | Feb-19            | Jan-19          |    11 | 3                     |
| ABC3   | Feb-19            | Jan-19          |    11 | 4                     |
| ABC1   | Mar-19            | Feb-19          |     6 | 8                     |
| ABC2   | Mar-19            | Feb-19          |    43 | 11                    |
| ABC3   | Mar-19            | Feb-19          |     6 | 11                    |
| ABC1   | Apr-19            | Mar-19          |    73 | 6                     |
| ABC2   | Apr-19            | Mar-19          |     2 | 43                    |
| ABC3   | Apr-19            | Mar-19          |     6 | 6                     |
+--------+-------------------+-----------------+-------+-----------------------+

A:

You appear to want lag().  This is complicated, because your dates are not well-formed, but that is fixable:
select t1.entity, t1.submissionperiod,
       lag(t1.submissionperiod) over (partition by t1.entity order by convert(date, '01-' + t1.submissionperiod)) as prev_submissionperiod,
       t1.value, 
       lag(t1.value) over (partition by t1.entity order by convert(date, '01-' + t1.submissionperiod)) as prev_submissionperiod,
from table1 t1

