Forum Discussion

ArslanManzoor's avatar
ArslanManzoor
Frequent Visitor
5 years ago
Solved

3 month rolling average data table

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!

 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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.