topic Re: Calculated Column with value of previous year in DAX Commands and Tips

Calculated Column with value of previous year

Anonymous — Thu, 11 Feb 2021 09:54:09 GMT

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!!!

Re: Calculated Column with value of previous year

rfigtree — Thu, 11 Feb 2021 09:21:45 GMT

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

Re: Calculated Column with value of previous year

Anonymous — Thu, 11 Feb 2021 09:30:03 GMT

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

Re: Calculated Column with value of previous year

rfigtree — Thu, 11 Feb 2021 09:36:27 GMT

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

Re: Calculated Column with value of previous year

Anonymous — Thu, 11 Feb 2021 09:44:59 GMT

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.

Re: Calculated Column with value of previous year

Anonymous — Thu, 11 Feb 2021 14:19:45 GMT

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.