topic Re: Calculate Change in percentage based on multiple Quarter Selection in DAX Commands and Tips

Calculate Change in percentage based on multiple Quarter Selection

nidhesh_tiwari — Wed, 24 Jul 2024 07:45:25 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.

Input Data:

Month-Year	Quarter-Year	SalesAmount
01-2023	Q1-2023	10
02-2023	Q1-2023	20
03-2023	Q1-2023	30
04-2023	Q2-2023	40
05-2023	Q2-2023	50
06-2023	Q2-2023	60
07-2023	Q3-2023	70
08-2023	Q3-2023	80
09-2023	Q3-2023	90
10-2023	Q4-2024	100
11-2023	Q4-2024	110
12-2023	Q4-2024	120

So when Q1-2023, Q3-2023 & Q4-2023 are selected from slicer, desired result should be i.e.

Quarter-Year	SalesAmount	% Change
Q1-2023	60	0.0%
Q3-2023	240	300%
Q4-2023	330	37.5%

but if only Q1-2023 & Q4-2023 are selected then the desired output should be

Quarter-Year	SalesAmout	% Change
Q1-2023	60	0.0%
Q4-2024	330	450%

@Greg @HotChilli @bhanu @bhanu_gautam @lbendlin

@Irwan

Many thanks

Re: Calculate Change in percentage based on multiple Quarter Selection

lbendlin — Mon, 05 Aug 2024 14:15:08 GMT

The simplest approach would be to use Visual Calculations ( particularly the PREVIOUS function). But you will want to invest in a proper data model first, with a proper calendar table.

Re: Calculate Change in percentage based on multiple Quarter Selection

Anonymous — Mon, 05 Aug 2024 16:49:03 GMT

Hi nidhesh_tiwari one of the best way to solve this dynamic thing is by using offset formula.
Source data

Current Amount

Previous amount using offset function

When All qtr is selected

When Random Qtrs are selected

if this solves your problem then accept the same as your solution.