Calculate monthly balance from YTD (multiple months)

[AliJ]

Hi All.

I have the data where i have columns by years for each month but its YTD balance. E.g., for year 2024, i have Jan YTD (whic is just 1 month), then Feb YTD (which is Jan+Feb), then Mar YTD (which is Jan+Feb+mar)... so on and on..

 I have these columns for mulitple years i.e. 2021, 2022, 2023, 2024.. so on.

I am not able to figure out how calculate monthly systematically where each year monthly is calculated by taking the YTD and subtracting preceding YTD column for all columns. E.g. to calculate Feb 2024 MTHly, it should take Feb YTD less Jan YTD. I know we can do new meausre 1 by 1 but that will be too many columns to add manually and have to do each month. See beow screen shot. Thanks 

[amitchandak]

@AliJ , Need following step.

In Power Query

1. Unpivot the month columns 

2. Remove YTD from the column , rename to Month Year

3. Create a new column and change data type to date

= "01 "& [Month Year]

In DAX create this column

Maxx(filter(table, Eomonth(Table[Month no]) = Eomonth(earlier(Table[Month No]),-1)), Table[Value])

if need add levels as per need

Maxx(filter(table, Eomonth(Table[Month no]) = Eomonth(earlier(Table[Month No]),-1) && [Level1] = earlier([Level1]) && [Level2] = earlier([Level2]) ), Table[Value])

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

add more levels if needed

Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[AliJ]

We were trying to do both ways meaning the screen shot i sent and this unpivot method (below screen shot where we add date attribute). Maybe our data structure is not that can accomodate. The data we have is just monthly YTD numbers (its a trial balance) with accounts. 

Does this make sense? 

[amitchandak · Accepted solution]

@AliJ , the this should work, add levels as needed

if([Month]=1, Table[Value],

Table[Value] -

Maxx(filter(table, Eomonth(Table[Date],0) = Eomonth(earlier(Table[Date]),-1) && [Level1] = earlier([Level1]) && [Level2] = earlier([Level2]) ), Table[Value]) )

[AliJ]

Thanks. This is very helpful. 

I am probably missing 1 more step. When i run this formula, it works for first months e.g. May YTD less Apr YTD but then its also doing Jun YTD Less Apr instead of Jun YTD less May YTD. I am sure its a dumb mistake but not an expert in this. Note, my first month is April and not Jan. Its April to March 12 months.  

Below is the table and query.  

Column = IF (

    [Month No.] = 1,

    'Trial Balance FY 2024 plus 2 years _ working'[Value],

    'Trial Balance FY 2024 plus 2 years _ working'[Value]

        - MAXX (

            FILTER (

                'Trial Balance FY 2024 plus 2 years _ working',

                EOMONTH ( 'Trial Balance FY 2024 plus 2 years _ working'[Month No.], 0 )

                    = EOMONTH ( EARLIER ( 'Trial Balance FY 2024 plus 2 years _ working'[Month No.] ), -1 )

                    && [FY] = EARLIER ( [FY] )

                    && [Fin_Book] = EARLIER ( [Fin_Book] )

                    && [CCID] = EARLIER ( [CCID] )

            ),

            'Trial Balance FY 2024 plus 2 years _ working'[Value]

        )

)

[AliJ]

Found the error. Now its fixed. Thanks for your help. Could not have done it without it. Below is updated

Column =

IF (

    [Month No.] <= 1,

    'Trial Balance FY 2024 plus 2 years _ working'[Value],

    'Trial Balance FY 2024 plus 2 years _ working'[Value]

        - MAXX (

            FILTER (

                'Trial Balance FY 2024 plus 2 years _ working',

                'Trial Balance FY 2024 plus 2 years _ working'[Month No.] <= EARLIER ( [Month No.] )

                    && 'Trial Balance FY 2024 plus 2 years _ working'[Month No.] = EARLIER ( [Month No.] ) - 1

                    && [FY] = EARLIER ( [FY] )

                    && [Fin_Book] = EARLIER ( [Fin_Book] )

                    && [CCID] = EARLIER ( [CCID] )

            ),

            'Trial Balance FY 2024 plus 2 years _ working'[Value]

        )

)