same period last year column

[bcobrien77]

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. 

[Anonymous]

@bcobrien77 

Hi,

I am trying to do something very similar to what you're doing, which is materializing the result of a measure into a column to be able to stratify/bucketize the values.  Did you ever find a solution to yours?

Regards,

Ferdinand

[v-yulgu-msft]

Hi @bcobrien77,

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

[bcobrien77]

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!