3 month rolling average data table

[ArslanManzoor]

Hi,

I posted a question on this forum a while back regarding getting 3 month rolling average data (answered by @Jihwan_Kim😞 Solved: 3 month rolling data visual - Microsoft Power BI Community

The attached .pbix was provided with the following DAX measure: 

However this doesn't seem to be giving me the correct average values in the table visual:

.pbix file:

https://www.dropbox.com/s/gwid24yyo4isxtx/arslanmanzoor.pbix?dl=0

For example, for May 2021, task A, the raw data values are 42, 42, 42, so should give an average value of 42. However in the visual I am getting an average of 40.63.

Is there a mistake in the DAX formula?

Thanks in advance!

[MFelix · Accepted solution]

Hi @ArslanManzoor ,

The measure you are calculating is the average value of the previous 3 months in this case you are getting for may the values between February and April and making the average so the data is:

If you make the average you get the 40.63.

To what I read you want to have the values of the montlhy average on the last 3 month so you want that if the value is on the last 3 month show the average otherwise do not show.

Redo your measure to:

TimeTakenAverageValue = 
VAR MaxFactDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALL ( 'Calendar' ) )
VAR Edate =
    EDATE ( MaxFactDate, -2 )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( AxisTable[Month & Year] ) = "Current",
            CALCULATE (
                [Time Taken Avg],
                FILTER (
                    ALL ( 'Calendar' ),
                    'Calendar'[Month & Year] <> MAX ( 'Calendar'[Month & Year] )
                )
            ),
        MAX ( 'Calendar'[Date] ) <= MaxFactDate
            && MAX ( 'Calendar'[Date] ) >= Edate, CALCULATE ( AVERAGE ( Data[Time taken] ) )
    )

check result below and in attach PBIX file.