cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Resident Rockstar

## Rolling 12 month average of Activity Count in Direct Query mode

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

1 ACCEPTED SOLUTION
Resident Rockstar

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
)
8 REPLIES 8
Community Support

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.

Resident Rockstar

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

Community Support

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.

Resident Rockstar

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

Community Support

Hi @SivaMani ,

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

Best regards

Amy Cai

Resident Rockstar

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],
__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.
Resident Rockstar

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
)
Frequent Visitor

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors