Previous Monthly Values by ID

[satkin]

Hi,

I have a table with IDs per month and a balance.  I'm wanting to have a summary of the balance x months ago, but seem to be going around in circles not getting anywhere.

Using the initial table below...

ID	Report Month	Balance
1	01/01/2022	1000
1	01/02/2022	1500
1	01/03/2022	2000
1	01/04/2022	1750
1	01/05/2022	2250
1	01/06/2022	2750
2	01/01/2022	700
2	01/02/2022	1400
2	01/03/2022	2100
2	01/04/2022	1900
2	01/05/2022	1850
2	01/06/2022	1950

I'd like to end up with...

ID	Report Month	Balance	1 Month Ago	6 Months Ago
1	01/01/2022	1000
1	01/02/2022	1500	1000
1	01/03/2022	2000	1500
1	01/04/2022	1750	2000
1	01/05/2022	2250	1750
1	01/06/2022	2750	2250	1000
2	01/01/2022	700
2	01/02/2022	1400	700
2	01/03/2022	2100	1400
2	01/04/2022	1900	2100
2	01/05/2022	1850	1900
2	01/06/2022	1950	1850	700

I've tried numerous approaches, but so far, nothing has worked.  Any advice would be greatly appreciated.

Many thanks

[rusgesig · Accepted solution]

Do you have a dedicated Calendar table?
You should create relationship between the date column you need and the date column on your calendar table.
You can create a Calendar by selecting create table and just typing in Calendar = CALENDARAUTO()
After you've done this create a relationship between the date columns in your modelling view.
You can then use a measure to calculate both.
CALCULATE(SUM([Balance])) will give you the current balance
CALCULATE(SUM([BALANCE]), DATEADD('Calendar'[Date], -6, MONTHS) should give you the previous value.
I'm just typing these off by heart so it might not be 100%, intellisense should get you through though.

[Samarth_18]

Hi @satkin ,

You can create a measure as below:-

1 month ago =
CALCULATE (
    SUM ( 'Table'[Balance] ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Report Month] )
            = MONTH ( EOMONTH ( MAX ( 'Table'[Report Month] ), -1 ) )
            && 'Table'[ID] = MAX ( 'Table'[ID] )
    )
)

6 month ago =
CALCULATE (
    SUM ( 'Table'[Balance] ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Report Month] )
            = MONTH ( EOMONTH ( MAX ( 'Table'[Report Month] ), -5 ) )
            && 'Table'[ID] = MAX ( 'Table'[ID] )
    )
)

Output:-