Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Kudoed Authors