Calculate Average of Last 3 months with Fiscal month = calendar month

Anonymous — Sun, 19 Feb 2023 05:33:14 GMT

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]

)

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

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

Re: Calculate Average of Last 3 months with Fiscal month = calendar month

lbendlin — Mon, 20 Feb 2023 01:19:57 GMT

You must add a calendar table to your data model. Ideally use an externally maintained table that conforms to your client's fiscal calendar. Don't try to recreate that in Power Query or DAX.

Re: Calculate Average of Last 3 months with Fiscal month = calendar month

Anonymous — Mon, 20 Feb 2023 07:39:32 GMT

Hi lbendlin ,

We don't have a date column at all in the data . ( Only till week level we have data)

Re: Calculate Average of Last 3 months with Fiscal month = calendar month

lbendlin — Mon, 20 Feb 2023 11:56:16 GMT

You must add a day column, even if you have to derive it from the first day of each week.

topic Re: Calculate Average of Last 3 months with Fiscal month = calendar month in DAX Commands and Tips

Calculate Average of Last 3 months with Fiscal month = calendar month

Re: Calculate Average of Last 3 months with Fiscal month = calendar month

Re: Calculate Average of Last 3 months with Fiscal month = calendar month

Re: Calculate Average of Last 3 months with Fiscal month = calendar month