cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Create a calculated column for prior year

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?

3 ACCEPTED SOLUTIONS
Super User

@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)

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Super User

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.

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

Super User

Hi @RingoMoon ,

You can do it in Power Query or using dax.

Power Query:

• Sort the table by event and by year
``````try
then

otherwise
null``````

DAX

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

SELECTCOLUMNS(
OFFSET(
-1,

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

3 REPLIES 3
Super User

Hi @RingoMoon ,

You can do it in Power Query or using dax.

Power Query:

• Sort the table by event and by year
``````try
then

otherwise
null``````

DAX

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

SELECTCOLUMNS(
OFFSET(
-1,

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

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.

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

Super User

@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)

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.