Calculate % Growth change on the basis of Selected Period

[nidheshtiwari]

Hi All, 

I am struggling to find the soultion for the below problem statement. I have to create a measure to calculate the %change from selected quarter range. For example, we have data of 2023 from each month with salesDate. 

 @Greg @HotChilli @bhanu @bhanu_gautam @lbendlin

@Irwan 

Many Thanks

[Anonymous · Accepted solution]

Hi @nidheshtiwari ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures. 

Rank = RANKX(ALLSELECTED('Table'[Quarter-Year]),[Quarter-Year],MAX('Table'[Quarter-Year]),ASC)

% Change = var a=[Rank]-1
var b=CALCULATE(SUM('Table'[Sales  Amount]),FILTER(ALLSELECTED('Table'),[Rank]=a))  
var c=SUM('Table'[Sales  Amount])
RETURN  DIVIDE(c-b,b,0)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[SachinNandanwar]

Is this what you are looking for ?

[sjoerdvn]

You might want to share a bit more about your model, are you using a date dimension?
At any rate, you will need some column in a year-quarter format to be able to determine the previous selected quarter.

[bhanu_gautam]

@nidheshtiwari , First create a measure

Total Sales Amount =
SUM('Sales'[SalesAmount])

Than create one more measure for percentage change

% Change =
VAR SelectedQuarters = VALUES('Sales'[Quarter-Year])
VAR FirstQuarter = MINX(SelectedQuarters, [Quarter-Year])
VAR PreviousQuarterSales =
CALCULATE(
[Total Sales Amount],
FILTER(
ALL('Sales'),
'Sales'[Quarter-Year] = FirstQuarter
)
)
RETURN
IF(
ISBLANK(PreviousQuarterSales),
0,
DIVIDE(
[Total Sales Amount] - PreviousQuarterSales,
PreviousQuarterSales,
0
)
)

Add quarter year file

[nidheshtiwari]

Hi Bhanu,

Thanks for reply but it is not working. I am getting 0.0% for all the selected quarters.

[bhanu_gautam]

% Change =
VAR SelectedQuarters = VALUES('Table'[Quarter-Year])
VAR CurrentQuarter = SELECTEDVALUE('Table'[Quarter-Year])
VAR PreviousQuarter =
CALCULATE(
MAX('Table'[Quarter-Year]),
FILTER(
ALL('Table'),
'Table'[Quarter-Year] < CurrentQuarter &&
'Table'[Quarter-Year] IN SelectedQuarters
)
)
VAR PreviousQuarterSales =
CALCULATE(
[Total SalesAmount],
'Table'[Quarter-Year] = PreviousQuarter
)
VAR CurrentQuarterSales = [Total SalesAmount]
VAR PercentageChange =
IF(
ISBLANK(PreviousQuarterSales),
BLANK(),
DIVIDE(CurrentQuarterSales - PreviousQuarterSales, PreviousQuarterSales, 0)
)
RETURN
IF(
ISBLANK(PreviousQuarter),
0,
PercentageChange
)