Last four quarters rate

[PeterChen]

I have a data like this:

I just show a simple table, but column Date is from 2015 to 2019/6/30, column Group has 4 groups (A-D), and column value is numeric that the numbers are different rather than all 5. What I want is create a measure to add up last four quarters. Therefore, if the max date in column Date is 2019/6/28, then the new measure, L4QrAll, is the total value add up from 2018Q3 to 2019Q2 (4 quarters) without considering groups. Suppose last 4 quarters total value of all groups is 50. Then, all should display 50.

I simply create fake result as below:

Next, another measure, L4QrEach, for each group, adding up last four quarters value. Last, I will calculate the rate, L4QrEach / L4QrAll.

YQ             Group    L4QrAll    L4QrEach   RATE

2018Q1     A            50            30              0.6

2018Q2     A            50            30              0.6

2018Q3     A            50            30              0.6

2018Q4     A            50            30              0.6

2019Q1     A            50            30              0.6

2019Q2     A            50            30              0.6

2018Q1     B            50            20              0.4

2018Q2     B            50            20              0.4

2018Q3     B            50            20              0.4

2018Q4     B            50            20              0.4

2019Q1     B            50            20              0.4

2019Q2     B            50            20              0.4

Finally, we get 0.6 for A and 0.4 for B. This is the only two values I want because I will use these rates to plot bar graph.

Above plot is what I want. X-axis is Group. Y-axis is Rate. There are 2 bars in each group because there are another column said group2. Any help?

[jbrijalba · Accepted solution]

If I understand you correctly you want to put all the Dates in to a YYYYQ format:

(YQ = Concatenate(DateField.[Year], Concatenate("Q", DateField[QuarterNo])) As a Column

Then you want a Total of the last 4 Quarters 

(L4QrAll = Calculate(Sum(ValueColumn), Filter(All(TableName), YQ  = // This is where I cannot make it dynamic however it will work but you will have to change it when we enter a new quater// "2019Q2" || YQ = "2019Q1" || YQ= "2018Q4" || YQ= "2018Q3")) As a Measure

Then a Measure for the Group Values

(L4QrEach = Calculate(Sum(ValueColumn), Filter(TableName, YQ  = "2019Q2" || YQ = "2019Q1" || YQ= "2018Q4" || YQ= "2018Q3")) As a Measure

Finally the Rate

Rate = Divide(L4QrEach, L4QrAll) As a Measure

Note:  This will not create a table like the one you had. This is due to level of detail. However, if you create a table like yours without the YQ field it will aggregate to the group level.

I hope this helps

This is the data I created to test it.

[PeterChen]

---

@jbrijalba wrote:

If I understand you correctly you want to put all the Dates in to a YYYYQ format:

(YQ = Concatenate(DateField.[Year], Concatenate("Q", DateField[QuarterNo])) As a Column

Then you want a Total of the last 4 Quarters 

(L4QrAll = Calculate(Sum(ValueColumn), Filter(All(TableName), YQ  = // This is where I cannot make it dynamic however it will work but you will have to change it when we enter a new quater// "2019Q2" || YQ = "2019Q1" || YQ= "2018Q4" || YQ= "2018Q3")) As a Measure

Then a Measure for the Group Values

(L4QrEach = Calculate(Sum(ValueColumn), Filter(TableName, YQ  = "2019Q2" || YQ = "2019Q1" || YQ= "2018Q4" || YQ= "2018Q3")) As a Measure

Finally the Rate

Rate = Divide(L4QrEach, L4QrAll) As a Measure

Note:  This will not create a table like the one you had. This is due to level of detail. However, if you create a table like yours without the YQ field it will aggregate to the group level.

I hope this helps

This is the data I created to test it.

---

Is it possible to use DATESINPERIOD?

L4QrAll = CALCULATE(SUM('dt'[Value]),DATESINPERIOD('dt'[date].[Date],MAX('dt'[date].[Date]),-4,QUARTER))

And then subset the last value. Not sure about this.

[jbrijalba]

I do not see why not. Duplicate the measure and give it a try. Maybe give Today() a try instead of MAX(date). I think either will work.

[PeterChen]

---

@jbrijalba wrote:

I do not see why not. Duplicate the measure and give it a try. Maybe give Today() a try instead of MAX(date). I think either will work.

---

After calculate last four quarters for adding up, how can I subset the last value in each group....

[jbrijalba]

Can you give more context? Not sure what you mean.