How to Calculate sum for previous reporting period

[Vick143]

Hi,

I have to create either a Measure or Calculated Column to get result displayed in Total Commission Previous Reporting Period but I have to be able to filter it. Reporting Periods basically gives snapshot of data, they are not countunious value so I created Rank column to get Total Commission for previous availabe reporting period based on specific Contract#. Contract#s are unique within a specific reporting period. Following table is just an example, my model is much, thousands of different Contracts and ten Reporting periods (attached below).

Thank you in advance.

Reporting Period	Contract#	Total Commison	Rank	Total Commission Previous Reporting Period
202310	888	$125	5	$120
202310	777	$133	5	$184
202309	888	$120	4	$160
202309	777	$184	4	$199
202308	888	$160	3	$130
202308	777	$199	3	$188
202307	888	$130	2	$142
202307	777	$188	1
202306	888	$142	1

ReportingPeriod

202112
202203
202206
202209
202212
202303
202306
202309
202310
202311

[FreemanZ · Accepted solution]

hi @Vick143 ,

not sure if i fully get you, try to add a calculated column like:

Column = 
MAXX(
    FILTER(
        data, 
        data[Contract#]=EARLIER(data[Contract#])
            &&data[Reporting Period] = EARLIER(data[Reporting Period] )   -1
    ),
    data[Total Commison]
)

it worked like:

[Vick143]

for my requirement, Instead of using Reporting Period I used Rank and it worked. Thank you very much.