Subtract Value at Aggregate by Quarter

Question

Values are for two groups by quarter.

In DAX, need to summarize all the data but also need to remove -3 from each quarter in 2021 for Group 1, without allowing the value to go below 0.

This only impacts:

Group 1 Only
2021 Only

However, I also need to retain the data details without the adjustment. So I can't do this in Power Query. My data detail is actually in months but I'm only listing one date per quarter for brevity.

Data:

Group	Date	Value
1	01/01/2020	10
1	04/01/2020	8
1	07/01/2020	18
1	10/01/2020	2
1	01/01/2021	12
1	04/01/2021	3
1	07/01/2021	7
1	10/01/2021	2
2	01/01/2020	10
2	04/01/2020	8
2	07/01/2020	18
2	10/01/2020	2
2	01/01/2021	12
2	04/01/2021	3
2	07/01/2021	7
2	10/01/2021	2

Result:

Group	Qtr/Year	Value
1	Q1-2020	10
1	Q2-2020	8
1	Q3-2020	18
1	Q4-2020	2
1	2020	38
1	Q1-2021	9
1	Q2-2021	0
1	Q3-2021	4
1	Q4-2021	0
1	2021	13
2	Q1-2020	10
2	Q2-2020	8
2	Q3-2020	18
2	Q4-2020	2
2	2020	2
2	Q1-2021	12
2	Q2-2021	3
2	Q3-2021	7
2	Q4-2021	2
2	2021	24

Kin Siang Kin Siang · Accepted Answer · 2021-06-11T14:45:17

You issue can be solved by using Matrix Table, and also to add new column to process value before create the table:

First, add a new column using following formula:

Revised value = 
var newValue = IF(YEAR(Sheet1[Date])=2021,Sheet1[Value]-3,Sheet1[Value])
return
IF(newValue <0,0,newValue)

Second, create the matrix table for the desired outcome:

Subtract Value at Aggregate by Quarter

1 Answers