topic Re: Average based on NMonth vs Month in DAX Commands and Tips

Average based on NMonth vs Month

Chandrashekar — Mon, 03 Jun 2024 06:52:13 GMT

Hello Team,

I need help with dax to get Average however I got it for count(Dax).

I need dax for average for below

average_Lmonth = Last Nmonth -1(Jan-24, Feb-24, Mar-24, Apr-24).

average = Lmonth(Jan-24, Feb-24, Mar-24, Apr-24,May-24)

Folder: Sample File

Regards,

Chandrashekar B

Re: Average based on NMonth vs Month

BeaBF — Mon, 03 Jun 2024 08:19:04 GMT

Chandrashekar Hi! Try with:

Average_Last_N_Months =

VAR CurrentDate = MAX('Sheet1'[Month_Year])

VAR LastNMonths = DATESINPERIOD(

Sheet1[Month_Year],

EOMONTH(CurrentDate, -1), -- End of last month

-4, -- Last 4 months excluding the current month

MONTH

)

RETURN

AVERAGEX(

CALCULATETABLE(

VALUES('Sheet1'),

LastNMonths

'Sheet1'[min_count]

)

Average_Current_Period =

VAR CurrentDate = MAX('Sheet1'[Month_Year])

VAR CurrentPeriod = DATESINPERIOD(

Sheet1[Month_Year],

CurrentDate,

-4, -- Last 5 months including the current month

MONTH

)

RETURN

AVERAGEX(

CALCULATETABLE(

VALUES('Sheet1'),

CurrentPeriod

'Sheet1'[min_count]

)

Re: Average based on NMonth vs Month

Chandrashekar — Mon, 03 Jun 2024 10:07:45 GMT

Hello,

Sorry not getting expected output. Expected out as below. Thanks in advance.

Output:

Input:

Regadrds,

Chandrashekar B

Re: Average based on NMonth vs Month

BeaBF — Mon, 03 Jun 2024 10:22:49 GMT

Chandrashekar Try:

Average_Feb24_to_Apr24 =

CALCULATE(

AVERAGE('Table'[SI NO]),

FILTER(

'Table',

'Table'[Month_Year] IN {"feb-24", "mar-24", "apr-24"}

)

Average_Feb24_to_Apr24_May24 =

CALCULATE(

AVERAGE('Table'[SI NO]),

FILTER(

'Table',

'Table'[Month_Year] IN {"feb-24", "mar-24", "apr-24", "may-24"}

)

Re: Average based on NMonth vs Month

Chandrashekar — Mon, 03 Jun 2024 11:25:51 GMT

Hello,

Month should be dynamic.

Regards,

Chandrashekar B

Re: Average based on NMonth vs Month

BeaBF — Mon, 03 Jun 2024 11:41:30 GMT

Chandrashekar ok, dynamic based on? selection? last solar month?

BBF

Re: Average based on NMonth vs Month

Chandrashekar — Mon, 03 Jun 2024 11:52:35 GMT

Hello,

Depends on selection from slicer. it may be old data(May be 2018 to till today.

Final Intention is to check how client is doing.

Regards,

Chandrashekar B

Regards,

Chandrashekar B

Re: Average based on NMonth vs Month

BeaBF — Mon, 03 Jun 2024 11:55:11 GMT

Chandrashekar

Average_Selected_Range =

VAR SelectedMonthss = VALUES('Table'[Month_Year])

RETURN

CALCULATE(

AVERAGE('Table'[SI NO]),

FILTER(

'Table',

'Table'[Month_Year] IN SelectedMonthss

)

Average_Previous_Range =

VAR SelectedMonths = VALUES('Table'[Month_Year])

VAR MaxMonth = MAX('Table'[Month_Year])

VAR PreviousMonths = EXCEPT(SelectedMonths, {MaxMonth})

RETURN

CALCULATE(

AVERAGE('Table'[SI NO]),

FILTER(

'Table',

'Table'[Month_Year] IN PreviousMonths

)

Re: Average based on NMonth vs Month

Chandrashekar — Tue, 04 Jun 2024 07:43:36 GMT

Hello,

Can we take average based on company instead of sl no.

Regards,

Chandrashekar B

Re: Average based on NMonth vs Month

Anonymous — Mon, 10 Jun 2024 08:46:04 GMT

Hi Chandrashekar

Thanks for the reply from BeaBF . The following is my testing for your reference.

I added some data in the table.

Measure = VAR _Last1month = CALCULATE ( COUNT ( 'Table'[Company] ), FILTER ( 'Table', [Month_Year] >= MIN ( slicer[Month_Year] ) && [Month_Year] <= DATE ( YEAR ( MAX ( slicer[Month_Year] ) ), ( MONTH ( MAX ( slicer[Month_Year] ) ) - 1 ), DAY ( MAX ( slicer[Month_Year] ) ) ) ) ) VAR _lastmonth = CALCULATE ( COUNT ( 'Table'[Company] ), FILTER ( 'Table', [Month_Year] >= MIN ( slicer[Month_Year] ) && [Month_Year] <= MAX ( slicer[Month_Year] ) ) ) RETURN DIVIDE ( _Last1month, _lastmonth )

Output:

Due to Security Policy, I am unable to open your link. If there is still a problem, please let me know the logic for calculating your desired result.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Average based on NMonth vs Month

Chandrashekar — Mon, 10 Jun 2024 09:15:22 GMT

Hello,

Thank you and will check.

Regards,

Chandrashekar B