Subtract Value at Aggregate by Quarter

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


Read more here: https://stackoverflow.com/questions/67930951/subtract-value-at-aggregate-by-quarter

Content Attribution

This content was originally published by gojomoso at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: