Hi Everyone,
I have a Sales dashboard with requirement to calculate average of last 3 months(including current month). The data does not have a date column, however has fiscal year, fiscal quarter, fiscal month & fiscal week columns. I have created a custom date column as below :
FY Date =
DATE(
'SalesGenie VN'[Fiscal Year], 'SalesGenie VN'[Fiscal Month]
,1
)
which gives date in below format : 01/MM/YYYY
Fiscal month has values as below . i.e) Jan =1 , Feb = 2 ...... Nov = 11, Dec = 12 . However Fiscal year is between May to April, which means that May 2023 (fiscal month = 5 ) is start of fiscal year and April 2024 ( fiscal month = 4 ) is end of fiscal year.
The client has data structured in this way , so can't change it.
I have a DAX created as below which works fine for a couple of months .
Average Sales Out Last 3 Months =
VAR v2 =
CALCULATE (
AVERAGEX (
VALUES ( 'SalesGenie VN'[Fiscal Month] ),
CALCULATE (
SUM ( 'SalesGenie VN'[Actual Sales in DMS] ),
'SalesGenie VN'[Data Source] = 1
)
),
DATESINPERIOD (
'SalesGenie VN'[FY Date],
MAX ( 'SalesGenie VN'[FY Date] ),
-3,
MONTH
),
ALL ('SalesGenie VN'[Fiscal Year],'SalesGenie VN'[Fiscal Quarter], 'SalesGenie VN'[Fiscal Month], 'SalesGenie VN'[Month Name]),ALL('Month Ranking'[Month])
)
RETURN
v2
The problem I am facing is that , If Jan is selected , by default Average is calculated as : Average ( Jan 2023, Dec 2022, Nov 2022) . Similarly for Feb , Average (Feb 2023, Jan 2023 , Dec 2022).
My requirement is that if Jan is selected, Average should be calculated as : Average ( Jan 2023, Dec 2023 , Nov 2023)
if Feb is selected, Average (Feb 2023, Jan 2023, Dec 2023)
if May is selected, Average ( May 2023, Apr 2022, Mar 2022) ........ taking into consideration Fiscal Yr
if Jun is selected, Average ( Jun 2023, May 2023, Apr 2022)
Could you please help in modifying the DAX which I have already written, to meet this requirement .. Or a new idea would also work.
Thanking you in advance ..
Swathi
@amitchandak @tamerj1 @FreemanZ @johnt75
