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.
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.
Solved! Go to Solution.
Hello,
It works. I done the below change in the calculation,
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.
@v-xicai ,
Thanks for your response.
I need a measure to calculate rolling 12 Months averge of Actvity Count.
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.
Averge DAX fuction only takes a column as a reference. We can't use Average(CountMeasure), right?
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
@v-xicai ,
Here is the measure that I tried,
Hello,
It works. I done the below change in the calculation,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |