Forum Discussion

satkin's avatar
satkin
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Previous Monthly Values by ID

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

Need Help
  • rusgesig's avatar
    rusgesig
    3 years ago

    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.

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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:-

     

     

  • rusgesig's avatar
    rusgesig
    Icon for Helper IV rankHelper IV
    3 years ago

    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.