Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Calculated Column with value of previous year

Hi everyone,

 

I have the following table: 

hey.PNG 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

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.

rfigtree
Resolver III
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).

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.