Forum Discussion
Helper ILast four quarters rate
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?
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.
5 Replies
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
Anonymous 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.