Calculated Column With Multiple Filters/Conditions?

[rbowen]

I'm trying to recreate an Excel spreadsheet with some specific calculations in it. I have a data table containing a set of accounts and their related months and dollar amounts:

I'd like to create (if possible) a calculated column that does the following for each account based on the Amount and Month columns:

If the value in the Month column =  September, copy the value for each account from the amount related to September

If the value in the Month column = October, subtract Septembers value from October's  [October] - [September]

If the value in the Month column  = November, subtract October's value from November's [November] - [October]

and so on thru to August.

For example, using the screenshot above, the September value for account 2710.92 would be $134,799,48.58. For October, it would be $131,588,712.52 - 134,799,149.58 = -3,210,437.06. Is it possible to do this in a single calculated column?

[amitchandak]

@rbowen , Make sure you convert month into date

date = Datevalue("01-"&[Month] & "-2023")

Then have a new column  like

New column =

var _date = [Date]

var _last = sumx(filter(Table, [ID] = earlier([ID]) && [MainAcc] = earlier([MainAcc]) && [Subract] = earlier([Subract])  &&eomonth([date]) = eomonth(_date ,-1)  ), [Amount])

return

Switch(True(),

[Month] in {"October", "November"}. [Amount] -_last,

[Amount]

)

Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s