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

Anonymous
Not applicable

## Calculated Column with value of previous year

Hi everyone,

I have the following table:

The table column is linked to another date table. I would like to insert a calculated column with the value of the same month (for the same Company_ID), but the year before. Is this possible and if so, which DAX formula do I use?

Thanks a lot!!!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I made a previous post where i asked for a dummy variable to show whether the revenue was bigger compared tot his year and this helped me. I am posting the code here:

``````Growth =
var __ThisMth = Revenue[Date]
var __ThisCo = Revenue[Company]
var __ThisStmt = Revenue[Statement]
var __PrevMth = DATEADD(Revenue[Date], -1, YEAR)
var __ThisValue = Revenue[Value]
var __PrevValue = LOOKUPVALUE(Revenue[Value],
Revenue[Company], __ThisCo,
Revenue[Statement], __ThisStmt,
Revenue[Date], __PrevMth)
RETURN
IF ( __ThisValue > __PrevValue, 1, 0)``````

All credits go to @dedelman_clng , I hope this can also help some other people that need the value of the previous year next to the current one.

5 REPLIES 5
Anonymous
Not applicable

I made a previous post where i asked for a dummy variable to show whether the revenue was bigger compared tot his year and this helped me. I am posting the code here:

``````Growth =
var __ThisMth = Revenue[Date]
var __ThisCo = Revenue[Company]
var __ThisStmt = Revenue[Statement]
var __PrevMth = DATEADD(Revenue[Date], -1, YEAR)
var __ThisValue = Revenue[Value]
var __PrevValue = LOOKUPVALUE(Revenue[Value],
Revenue[Company], __ThisCo,
Revenue[Statement], __ThisStmt,
Revenue[Date], __PrevMth)
RETURN
IF ( __ThisValue > __PrevValue, 1, 0)``````

All credits go to @dedelman_clng , I hope this can also help some other people that need the value of the previous year next to the current one.

Resolver III

=DAte(YEAR(Table1[date]-1),MONTH(Table1[date]),DAY(Table1[date]))

Anonymous
Not applicable

Hi, this returns the date, I need the column to return the corresponding Value of that date (Column 4).

Resolver III

maybe something like this but if multiple values on that date will give you largest value.

=calculate(max(othertable[value]),filter(all(othertable),othertable[date]=DAte(YEAR(Table1[date]-1),MONTH(Table1[date]),DAY(Table1[date]))

Anonymous
Not applicable

So with othertable you're referring to the Date-table, correct? Value is not a Column in that Date-table. This date-table is just one column of dates to be able to make use of the time-intelligence functions.

I also just tried your formula, and unfortunately it just returns the same value.

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.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors