YTD Trial Balance to Monthly Activity

[Sean6520]

Hello,

I have a simple trial balance in which the income statement portion of it is YTD balances and I would like to convert it to monthly activity.  There are a few other posts on this, however this seems to be the best solution for me - if I could get it across the finish line.

I am getting an error stating: Semantic Error: EARLIER/EARLIEST refers to an earlier row context which doesn't exist

The column headers are Account, Debit, Credit, Closing Date

First, I have Amount:=sum(TrialBalance[Debit])-sum(TrialBalance[Credit])

Then, I have this which I don't fully understand, but I believe it will work if tweaked:

Monthly Amount New:=VAR _PDATE =

CALCULATE(

MAX(TrialBalance[ClosingDate]),

CALCULATETABLE(

ALLSELECTED('TrialBalance'),

TrialBalance[Account]=(TrialBalance[Account]),

// 'dataset'[Entity] = ('dataset'[Entity]),

TrialBalance[ClosingDate]< EARLIER(TrialBalance[ClosingDate])

)

)

VAR _PYTD =

CALCULATE( MAX([Amount]),

CALCULATETABLE(

ALLSELECTED('TrialBalance'),

TrialBalance[Account]=EARLIER(TrialBalance[Account]),

// 'dataset'[Entity] = EARLIER('dataset'[Entity]),

TrialBalance[ClosingDate] = _PDATE

)

)

VAR YR =YEAR(_PDATE)

VAR CYR =YEAR(TrialBalance[ClosingDate])

RETURN

IF(

YR < CYR,

[Amount],

[Amount] - _PYTD

)

Any help would greatly be appreciated!!  Thank you!

[Sean6520]

Ah - maybe this was obvious, but I thought this would be a calculated measure - it isn't, it is a calculated column!  It works now 🙂  Thank you for your help!!

[vaibhavkale570]

hello

can you try below:

Monthly Amount New :=
VAR _PDATE =
CALCULATE(
MAX(TrialBalance[ClosingDate]),
ALLEXCEPT(TrialBalance, TrialBalance[Account])
)

VAR _PYTD =
CALCULATE(
MAX([Amount]),
FILTER(
ALL(TrialBalance),
TrialBalance[Account] = EARLIER(TrialBalance[Account]) &&
TrialBalance[ClosingDate] = _PDATE
)
)

VAR YR = YEAR(_PDATE)
VAR CYR = YEAR(TrialBalance[ClosingDate])

RETURN
IF(
YR < CYR,
[Amount],
[Amount] - _PYTD
)

[Sean6520]

Hey Vaibhakale,

Thank you for the reply!  Unfortunately, I get the same error.  "Semantic Error: EARLIER/EARLIEST refers to an earlier row context which doesn't exist".  I wonder if it has anything to do with my data dates?  There has to be a start and and end at some points...the screen shot below is the month number/years in my data set just for reference.  That may be the wrong trail to go down, but was a thought...

FYI - the sums are equally zero as they should as it is a trial balance.