Create measure to calculate year quarter growth rate

[PeterChen]

I have a data looks like this:  

Date          F1      F2       Value
2014/1/1      A       A1         0.5
2014/1/1      A       A1         0.5
2014/5/1      A       A1         1
2015/1/3      A       A1         0.6
2015/3/5      A       A1         0.6
2015/4/3      A       A1         1.4
2014/3/1      B       B5         0.3
2014/3/2      B       B5         0.7
2014/6/1      B       B5         1
2015/1/3      B       B5         0.7
2015/3/9      B       B5         0.7
2015/6/3      B       B5         1.5

I want to create a measure to calculate the growth rate, so I can create dynamic plot and table in the report tab and it will automatically calculate the growth rate if I change the Date to hierarchy, year and quarter, or add one more column, F1 or F2, to the values.

[v-lili6-msft]

hi, @PeterChen 

You could use DATEADD Function to create a measure as below:

Measure = 
	VAR __PREV_QUARTER = CALCULATE(SUM('Table'[Value]), DATEADD('Date'[Date], -1, QUARTER))
	RETURN
		DIVIDE(SUM('Table'[Value]) - __PREV_QUARTER, __PREV_QUARTER)

Best Regards,

Lin

[PeterChen]

---

@v-lili6-msft wrote:

hi, @PeterChen 

You could use DATEADD Function to create a measure as below:

Measure = 
	VAR __PREV_QUARTER = CALCULATE(SUM('Table'[Value]), DATEADD('Date'[Date], -1, QUARTER))
	RETURN
		DIVIDE(SUM('Table'[Value]) - __PREV_QUARTER, __PREV_QUARTER)

Best Regards,

Lin

---

What does this come from? How did you create 'Date'? Could you please explain more on it?

'Date'[Date]

[v-lili6-msft · Accepted solution]

hi, @PeterChen 

DATEADD is Time-intelligence function, It's better use a complete dim date table in calculation.

Create a dim date table, then create a relationship with basic data table

And then use dim fields from dim table instead of data table.

And I would recommend you learn DAX first.

https://www.sqlbi.com/p/introducing-dax-video-course/

https://www.sqlbi.com/books/the-definitive-guide-to-dax/

Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

Best Regards,

Lin