topic Re: Calculate % Growth change on the basis of Selected Period in DAX Commands and Tips

Calculate % Growth change on the basis of Selected Period

Anonymous — Wed, 24 Jul 2024 08:20:32 GMT

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

Re: Calculate % Growth change on the basis of Selected Period

bhanu_gautam — Wed, 24 Jul 2024 08:34:20 GMT

Anonymous , 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

Re: Calculate % Growth change on the basis of Selected Period

sjoerdvn — Wed, 24 Jul 2024 09:03:59 GMT

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.

Re: Calculate % Growth change on the basis of Selected Period

Anonymous — Wed, 24 Jul 2024 09:16:42 GMT

Hi Bhanu,

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

Re: Calculate % Growth change on the basis of Selected Period

bhanu_gautam — Wed, 24 Jul 2024 10:13:51 GMT

% 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
)

Re: Calculate % Growth change on the basis of Selected Period

SachinNandanwar — Thu, 25 Jul 2024 07:52:16 GMT

Is this what you are looking for ?

Re: Calculate % Growth change on the basis of Selected Period

Anonymous — Mon, 29 Jul 2024 08:14:21 GMT

Hi Anonymous ,

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.