3 months rolling average value

[Anonymous]

I am trying to get the average values for previous month, current month and the next month...

and I am using this formula but for some reason 3 months rolling average values are wrong

This value is being used to show average 3month rolling price on each month. can somebody give me better code or at least let me know what I am doing wrong here? I believe the date values are correct.. I am thinking it's erither currentDate variable or.. calculation function that are wrong. thanks in advance

3MonthRollingAverage =

VAR CurrentDate = MIN('175HPTractors'[DATESEEN].[Date])

VAR StartDate = EOMONTH(CurrentDate, -2) + 1  -- Start of previous month

VAR EndDate = EOMONTH(CurrentDate, 2) -1  -- End of next month

RETURN

    CALCULATE(

        AVERAGE('175HPTractors'[PRICE]),

        FILTER(

            ALL('175HPTractors'[DATESEEN].[Date]),

            '175HPTractors'[DATESEEN].[Date] >= StartDate &&

            '175HPTractors'[DATESEEN].[Date] <= EndDate

        )

    )

[muhammad_786_1 · Accepted solution]

Hi @Anonymous 

You can make a few modifications to your DAX formula, and it should work correctly for you. I have also compared the results, which you can see in the attached screenshot and Power BI file for verification.

3MonthRollingAverage = 
VAR CurrentDate = MIN('175HPTractors'[DATESEEN])
VAR StartDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) - 2, 1) -- StartDate of previous month
VAR EndDate = EOMONTH(CurrentDate, 1) -- EndDate of next month
RETURN
    CALCULATE(
        AVERAGE('175HPTractors'[PRICE]),
        FILTER(
            ALL('175HPTractors'),
            '175HPTractors'[DATESEEN] >= StartDate &&
            '175HPTractors'[DATESEEN] <= EndDate
        )
    )

File

Best Regards,
Muhammad Yousaf

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

LinkedIn

[Kedar_Pande]

@Anonymous 

Revised 3-Month Rolling Average Measure

3MonthRollingAverage =
VAR CurrentDate = MIN('175HPTractors'[DATESEEN])
VAR StartDate = EOMONTH(CurrentDate, -1) + 1 
VAR EndDate = EOMONTH(CurrentDate, 1) 
RETURN
CALCULATE(
AVERAGE('175HPTractors'[PRICE]),
FILTER(
ALL('175HPTractors'[DATESEEN]),
'175HPTractors'[DATESEEN] >= StartDate &&
'175HPTractors'[DATESEEN] <= EndDate
)
)

*Ensure you have a proper Date table in your model and that all date-related calculations use it. Using a Date table helps avoid errors in time-based calculations.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Ashish_Mathur]

Hi,

This DAX measure patern will work

Measure = averagex(datesbetween(calendar[date],edate(min(calendar[date]),-1),eomonth(max(calendar[date]),1)),'175HPTractors'[PRICE])

To your visual, drag Year and Month name from the Calendar Table.

If this does not work, then share the download link of the PBI file.  Show the excpeted result there very clearly.

[Anonymous · Accepted solution]

Hi @Anonymous 

You can use the DATESINPERIOD function, which returns the date of the period:

rolling average = CALCULATE(
AVERAGE('Table'[value]),
DATESINPERIOD(
'Table'[Date],
MAX('Table'[Date]),
-3,
MONTH
)
)

Best Regards,

Jayleny

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

[muhammad_786_1 · Accepted solution]

Hi @Anonymous 

You can make a few modifications to your DAX formula, and it should work correctly for you. I have also compared the results, which you can see in the attached screenshot and Power BI file for verification.

3MonthRollingAverage = 
VAR CurrentDate = MIN('175HPTractors'[DATESEEN])
VAR StartDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) - 2, 1) -- StartDate of previous month
VAR EndDate = EOMONTH(CurrentDate, 1) -- EndDate of next month
RETURN
    CALCULATE(
        AVERAGE('175HPTractors'[PRICE]),
        FILTER(
            ALL('175HPTractors'),
            '175HPTractors'[DATESEEN] >= StartDate &&
            '175HPTractors'[DATESEEN] <= EndDate
        )
    )

File

Best Regards,
Muhammad Yousaf

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

LinkedIn

[hnguy71]

Hi @Anonymous 

I would assume that it would likely be your CurrentDate variable. It's hard to tell without some visual indicators but because the MIN or MAX of a value within a visual context could be deceiving and be applied per value shown, that's likely your culprit. Perhaps try to adjust the CurrentDate variable to something like this:

VAR CurrentDate = CALCULATE(MIN('175HPTractors'[DATESEEN].[Date]), ALLSELECTED()) 

If you could spare some time to build a sample input and output we could perhaps see the issue more clearly.