Rolling 6 months

[jimpatel]

Hi,

Thanks a lot for looking at my post.

I am using below code (Thanks to Forum community). May i know how can i change this to show 6 months rolling average please?

Thanks a lot

Final result = VAR _date = SELECTEDVALUE ( 'Table'[Due Date] ) VAR _partmonth = CALCULATE ( COUNT ( 'Table'[Due Date] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR('Table'[Due Date])=YEAR(_date) && MONTH ( 'Table'[Due Date] ) = MONTH ( _date ) && 'Table'[Time to Close] = 2 ) ) VAR _month = CALCULATE ( COUNT ( 'Table'[Due Date] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR('Table'[Due Date])=YEAR(_date) && MONTH ( 'Table'[Due Date] ) = MONTH ( _date ) ) ) RETURN IF ( ISBLANK ( _partmonth ), 0, DIVIDE ( _partmonth, _month ) )

[Anonymous]

HI @jimpatel,

You can try to use the following measure formulas if it suitable for your requirement:

formula =
VAR _currDate =
    MAX ( 'Table'[Due Date] )
VAR summary =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Due Date]
                >= DATE ( YEAR ( _currDate ), MONTH ( _currDate ) - 6, DAY ( _currDate ) )
                && [Due Date] <= _currDate
        ),
        [Due Date],
        "Div",
            VAR cDate =
                MAX ( 'Table'[Due Date] )
            RETURN
                CALCULATE (
                    DIVIDE (
                        CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Time to Close] = 2 ),
                        COUNTROWS ( 'Table' )
                    ),
                    ALLSELECTED ( 'Table' ),
                    YEAR ( 'Table'[Due Date] ) = YEAR ( cDate )
                        && MONTH ( 'Table'[Due Date] ) = MONTH ( cDate )
                )
    )
RETURN
    AVERAGEX ( summary, [Div] )

Regards,

Xiaoxin Sheng

[jimpatel]

Thanks a lot for your effort and time. But this formula does not work well for me. When i apply above formula to get last 6 months rolling average, i am getting below answers which is not right.

Any idea please?

thanks a lot

[lbendlin]

Second request: Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

That formula only tells you how far you got in a particular month  (how many days in you are), and it's rather inefficient.  You could have just used the day number and divide it by the count of days in the month.

A 6 month rolling average would not make sense in this context.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...