cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.