Forum Discussion
same period last year column
Hi,
I am struggling with when to use measures vs calculated columns.
I have 60K records of data where I am trying to calculate the change in rents (year over year ).
I have easily created measures to do this but I need to categorize these changes into
1) Rent Increase, Decrease or no change
2) Stratify the increases/decreases by 100 increments (0-100, 101-200)
When I try to create a column to calculate the same period last year I get a circular depdendcy errror.
What I want to know is if this is how I should be doing this type of calculation and if not what a good approach would be in Power BI.
Thanks in advance.
3 Replies
v-yulgu-msftMicrosoft Employee
Hi Anonymous,
With current information, I cannot imagine your table structure. Please provide some sample data and show us your desired output with simple example so that I can test for you. Also, you can show the measure formula you have created.
Regards,
Yuliana Gu
Hi,
The data is structured like this
Customer Year Rents 1 2012 100 2 2012 105 4 2013 165 5 2013 170 1 2014 200 The output would look like this
2012 to 2013 2013-2014 2014-2015 Customer Year 1 Changes Year 2 Changes Year 3 Changes Rent Decreases Count of customers with Rent Decreases Rent Increases Count of customers with Increases No Change In Rent Rent Increases <25 Count of customers with Increases <25 25 to 50 Count of customers with Increases 25 to 50 50 to 100 Count of customers with Increases 50 to 100 100 to 200 Count of customers with Increases 100 to 200 >200 Count of customers with Increases >200 One other thing to keep in mind is that not every customer exists in every year but the concepts would stay the same.
Measures Created
Yearly Rents = sum(DATA_FOR_POWER_BI_CSV[Rent_Total])
Total Rents Prior Year = CALCULATE([Yearly Rents],SAMEPERIODLASTYEAR(DATA_FOR_POWER_BI_CSV[Fiscal Year]))
Year Over Year Rent Difference = if(ISBLANK([Total Rents Prior Year]),0 ,[Yearly Rents]-[Total Rents Prior Year])
Thanks so much!