Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

3 months rolling average value

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
        )
    )
Need Help
  • muhammad_786_1's avatar
    muhammad_786_1
    1 year ago

    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

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

5 Replies

  • hnguy71's avatar
    hnguy71
    Super User
    1 year ago

    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.

  • muhammad_786_1's avatar
    muhammad_786_1
    Solution Supplier
    1 year ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    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.

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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