Hello:
I have a dataset with columns (Fiscal Year, Fiscal Quarter, Calendar Year, Calendar Quarter, Sales, Indicator) - Sample data attached.
For each Indicator (eg. "xx") and user choice of Year & Quarter (Q1/Q2/Q3/Q4), I would like to sum up 4 quarters (starting with the user choice of year and quarter) and find the average quarterly sales as output for each custom year.
I came up with the following but it is not working in all situations such as for eg. if the user selects Oct-Dec quarter of 2021, then the sum would be of the Oct-Dec 2021, Jan-Mar 2022, Apr-Jun 2022 and Jul-Sep 2022 quarters i.e. when the calculation involves quarters spread over two consecutive years.
VAR __Quarter = SELECTEDVALUE('Quarter'[Quarter])
VAR __Year = SELECTEDVALUE('Year'[Year])
VAR __Result =
SUMX(
FILTER(
'Data',
YEAR('Data'[Date]) = __Year &&
'Data'[Quarter] >= __Quarter &&
'Data'[Quarter] <= __Quarter + 3
),
'Data'[Value]
)
RETURN
__Result
I am not sure if what I am attempting to do is possible within Power BI. Any guidance would be much appreciated. Thanks in advance.
| Fyear | Fquarter | Cyear | Cquarter | Sales | Indicator | Country |
| 2022 | Q2 | 2021 | Q4 | 100 | xx | A |
| 2022 | Q1 | 2021 | Q3 | 250 | xx | C |
| 2022 | Q1 | 2021 | Q3 | 600 | xy | C |
| 2022 | Q3 | 2022 | Q1 | 50 | xz | A |
| 2022 | Q4 | 2022 | Q2 | 250 | xy | D |
| 2022 | Q1 | 2022 | Q3 | 600 | xz | B |
| 2022 | Q1 | 2022 | Q3 | 450 | xx | D |
| 2022 | Q3 | 2022 | Q1 | 550 | xx | E |
| 2022 | Q2 | 2022 | Q4 | 300 | xx | E |
| 2022 | Q1 | 2022 | Q3 | 150 | xz | C |
| 2022 | Q1 | 2022 | Q3 | 450 | xx | B |
| 2022 | Q2 | 2022 | Q4 | 500 | xx | A |
| 2022 | Q2 | 2022 | Q4 | 320 | xx | A |
| 2022 | Q1 | 2022 | Q1 | 150 | xy | D |
| 2022 | Q3 | 2022 | Q3 | 170 | xz | B |
