Subtract the Sum of Two Columns based on Preceding and Current Selected Value

[Anonymous]

I have a Database Table with Columns Quarter and Quantity. Minimizing the actual data with 30K entries, I have simplified it into the table below:

Quarter	Quantity
2022Q4	2464
2022Q4	2525
2023Q1	5534
2023Q1	1421
2023Q1	1351
2023Q1	3461
2023Q2	6161
2023Q2	124
2023Q2	516

I have finished the task using multiple DAX, but I want to specifically create a table that will generate/update upon changing only the Quarter Filter. Suppose the selected value in the Quarter Filter is 2023Q2. I want to display a column that shows the difference of the sum of the Quantity of the current selected Quarter (2023Q2) subtracted with the sum of the Quantity of the preceding Quarter (2023Q1).

I want the result to be:

Filtered Quarter: 2023Q2

Sum of Quantity	2023Q2 - 2023Q1
6801	-1505

This is the DAX that I have come up with for Column 2023Q2 - 2023Q1:

Selected Quarter QuantitySum - Preceding Quarter QuantitySum = 
CALCULATE(
	SUM('Database'[Quantity]),
	'Database'[Quarter] IN { SELECTEDVALUE('Database'[Quarter]) }
	) - CALCULATE(
		SUM('Database'[Quantity]),
		'Database'[Quarter] IN { ---PRECEDING QUARTER--- }
		)

Cheers!

[Ashish_Mathur]

Hi,

Create another column in the table with the first month of each quarter.  Share that revised table.

[Anonymous]

Hi Ashish, thanks for your response. Could you please explain why I need to create the said column? I am aiming to accomplish this task by creating only one DAX if it's possible.

Quarter	Starting Month	Quantity
2022Q1	January	xxx
2022Q2	April	xxx
2022Q3	July	xxx
2022Q4	October	2464
2022Q4	October	2525
2023Q1	January	5534
2023Q1	January	1421
2023Q1	January	1351
2023Q1	January	3461
2023Q2	April	6161
2023Q2	April	124
2023Q2	April	516

[Ashish_Mathur]

Hi,

That additional column will allow creation of a Date column.  This in turn will allow creation of a Calendar Table.  We will then be able to use the Date Intelligence functions.  Key in values in place of XXX and share the revised table.

[Anonymous]

Hello,

Here is the revised table. 

Quarter	Starting Month	Quantity
2022Q1	January	251
2022Q2	April	3671
2022Q3	July	6237
2022Q4	October	2464
2022Q4	October	2525
2023Q1	January	5534
2023Q1	January	1421
2023Q1	January	1351
2023Q1	January	3461
2023Q2	April	6161
2023Q2	April	124
2023Q2	April	516

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.