Create a calculated column for prior year

[RingoMoon]

Let's say I have this table below

Event	Year	Revenue
Event A	2020	10000
Event A	2021	8000
Event A	2022	11000
Event A	2024	5000

Notice that there is no revenue for 2023. I want to create a new calculated column called "Prior Year". The logic for the prior year column would be based on whether that prior year sales is blank or not. If the prior year sales is not blank then return that as the prior year, otherwise if it is blank then go back another year until a non blank year is found. So in the scenario above, the year 2024 shoud have 2022 as its prior year due to the fact that 2023 has no revenue. So my output table would look like below.

Event	Year	Revenue	Prior Year
Event A	2020	10000	blank
Event A	2021	8000	2020
Event A	2022	11000	2021
Event A	2024	5000	2022

How do I achieve this?

[bhanu_gautam · Accepted solution]

@RingoMoon , Try using below method create a new calculated column, replace column and table name 

Prior Year =
VAR CurrentYear = [EventYearRevenue]
VAR PriorYearRevenue =
CALCULATE(
MAX([EventYearRevenue]),
FILTER(
ALL('TableName'),
[EventYearRevenue] <> BLANK() && [EventYearRevenue] < CurrentYear
)
)
RETURN
IF(ISBLANK(PriorYearRevenue), BLANK(), PriorYearRevenue)

[Alex87 · Accepted solution]

This should do the trick

Prior Year = 
VAR _CurrentYear = SolutionAlex[Year]
VAR _PriorYear = _CurrentYear - 1
VAR _FindYear = 
    CALCULATE(
        MAX(SolutionAlex[Year]),
        SolutionAlex[Year] < _CurrentYear,
        NOT(ISBLANK(SolutionAlex[Revenue]))
    )
VAR _Value =  CALCULATE(SUM(SolutionAlex[Revenue]), ALL(SolutionAlex), SolutionAlex[Year] = _FindYear)
VAR _Result = 
IF(
    ISBLANK(_Value),
    BLANK(),
    _Value
)
RETURN
_FindYear

If you want the Prior Year, then return _FindYear as in my DAX, if you want the associated revenues, return _Result.

If it answers your query, please mark my reply as the solution. Thanks!

Don't forget to change the table name 'SolutionAlex' with your tableName

[MFelix · Accepted solution]

Hi @RingoMoon ,

You can do it in Power Query or using dax.

Power Query:

• Sort the table by event and by year
• Add the following column

try
  if #"Added Index"{[Index]-1}[Event] = [Event]
then
 #"Added Index"{[Index]-1}[Revenue] else null

otherwise 
null

DAX

Add the following column:

VAR temp_table = SUMMARIZECOLUMNS(
		'Table'[Event],
		'Table'[Year],
		'Table'[Revenue]
	)
	RETURN

		SELECTCOLUMNS(
			OFFSET(
				-1,

				temp_table,
				ORDERBY('Table'[Year]),
				,
				PARTITIONBY('Table'[Event])
			),
			'Table'[Revenue]
		)

[Alex87 · Accepted solution]

This should do the trick

Prior Year = 
VAR _CurrentYear = SolutionAlex[Year]
VAR _PriorYear = _CurrentYear - 1
VAR _FindYear = 
    CALCULATE(
        MAX(SolutionAlex[Year]),
        SolutionAlex[Year] < _CurrentYear,
        NOT(ISBLANK(SolutionAlex[Revenue]))
    )
VAR _Value =  CALCULATE(SUM(SolutionAlex[Revenue]), ALL(SolutionAlex), SolutionAlex[Year] = _FindYear)
VAR _Result = 
IF(
    ISBLANK(_Value),
    BLANK(),
    _Value
)
RETURN
_FindYear

If you want the Prior Year, then return _FindYear as in my DAX, if you want the associated revenues, return _Result.

If it answers your query, please mark my reply as the solution. Thanks!

Don't forget to change the table name 'SolutionAlex' with your tableName

[bhanu_gautam · Accepted solution]

@RingoMoon , Try using below method create a new calculated column, replace column and table name 

Prior Year =
VAR CurrentYear = [EventYearRevenue]
VAR PriorYearRevenue =
CALCULATE(
MAX([EventYearRevenue]),
FILTER(
ALL('TableName'),
[EventYearRevenue] <> BLANK() && [EventYearRevenue] < CurrentYear
)
)
RETURN
IF(ISBLANK(PriorYearRevenue), BLANK(), PriorYearRevenue)