Forum Discussion

isam2003's avatar
isam2003
Icon for Helper I rankHelper I
3 years ago
Solved

Calculations over Custom Year

Calculations over Custom Year
2 seconds ago

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. 

FyearFquarterCyearCquarterSalesIndicatorCountry
2022Q22021Q4100xxA
2022Q12021Q3250xxC
2022Q12021Q3600xyC
2022Q32022Q150xzA
2022Q42022Q2250xyD
2022Q12022Q3600xzB
2022Q12022Q3450xxD
2022Q32022Q1550xxE
2022Q22022Q4300xxE
2022Q12022Q3150xzC
2022Q12022Q3450xxB
2022Q22022Q4500xxA
2022Q22022Q4320xxA
2022Q12022Q1150xyD
2022Q32022Q3170xzB

1 Reply