Rolling 12 month average of Activity Count in Direct Query mode

[SivaMani]

Hello,

I'm trying to calculate rolling 12 month average of Activity count. Here is the sample data,

Act #	Date
A1	01-01-2019
A2	01-01-2019
A3	01-01-2019
A4	01-01-2019
A5	01-01-2019
A6	01-01-2019
A7	01-01-2019
A8	01-01-2019
A9	01-01-2019
A10	01-01-2019
A11	01-01-2019
A12	01-01-2019
A13	01-01-2019
A14	01-01-2019
A15	01-01-2019
A16	01-02-2019
A17	02-02-2019
A18	03-02-2019
A19	04-02-2019
A20	05-02-2019
A21	06-02-2019
A22	07-02-2019
A23	08-02-2019
A24	09-02-2019
A25	10-02-2019

The output that I'm looking for is,

Month	Rolling 12M Avg
Jan-19	15
Feb-19	12.5

Please assist me.

[v-xicai]

Hi @SivaMani ,

you can try to create measures like DAX below.

Period End = LASTDATE(Table1[Date])

Period Start= FIRSTDATE( DATESINPERIOD(Table1[Date], [Period End], -12, MONTH))

Rolling 12M Avg = CALCULATE(AVERAGE(Table1[Act #]),DATESBETWEEN ( Table1[Date], [Period Start], [Period End] ))

Best Regards,

Amy

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

[SivaMani]

@v-xicai ,

Thanks for your response.

I need a measure to calculate rolling 12 Months averge of Actvity Count.

[v-xicai]

Hi @SivaMani ,

Then you can change the last measure in DAX below.

Rolling 12M Avg = CALCULATE(AVERAGE(Table1[Actvity Count]),DATESBETWEEN ( Table1[Date], [Period Start], [Period End] ))

Best Regards,

Amy

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

[SivaMani]

Averge DAX fuction only takes a column as a reference. We can't use Average(CountMeasure), right?

[v-xicai]

Hi @SivaMani ,

Yes, you are right, while AVERAGEX(Table1,[Actvity Count]) will be ok.

 You can learn more: https://docs.microsoft.com/zh-cn/dax/averagex-function-dax

Best regards

Amy Cai

[SivaMani]

@v-xicai ,

Here is the measure that I tried,

Count of Activity # rolling average =

    VAR __LAST_DATE = ENDOFMONTH(DM_DAY_Activity_D[CLDR_DATE])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            DM_DAY_Activity_D[CLDR_DATE],

            STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),

            __LAST_DATE

        )

VAR __RESULT = AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES(DM_DAY_Activity_D),

                    DM_DAY_Activity_D[CAL_YEAR],

                    DM_DAY_Activity_D[CAL_QTR],

                    DM_DAY_Activity_D[CAL_MONTH_ID],

                    DM_DAY_Activity_D[CAL_MONTH_DESC]

                ),

                __DATE_PERIOD

            ),

            CALCULATE(

                COUNTA(Activity[Activity Number]),

                ALL(DM_DAY_Activity_D[DAY_NUM_CAL_MONTH])

            )

        )

    RETURN

__RESULT

I'm getting this error - Error Message:
MdxScript(Model) (9, 3) Calculation error in measure 'Activity'[Count of Activity # rolling average]: An invalid numeric representation of a date value was encountered.

[SivaMani · Accepted solution]

Hello,

It works. I done the below change in the calculation,

 VAR __LAST_DATE = MAX(DM_DAY_Activity_D[CLDR_DATE])

    VAR __DATE_PERIOD =

        FILTER(

            DM_DAY_Activity_D, DM_DAY_Activity_D[CLDR_DATE] >=

            __LAST_DATE-365,

            DM_DAY_Activity_D[CLDR_DATE] <=__LAST_DATE

        )

[Kisame01]

Hi SivaMani,

Do you perhaps have an example of this solution. If not I would just like to know on your below code, is this column DM_DAY_Activity_D[CLDR_DATE]  an actual date column? Because when I try this i get an error 

Dax comparison operations do not support comparing values or FORMAT function to convert one of the values.