cancel
Showing results for
Did you mean:
Frequent Visitor

## How to get monthly average value using Dax?

Hi Community!

I want to get average value in the past month.

Anyone knows how to do this using Dax?

For example, I have a database like this:

Let's assume today date is 2022/11/09. So for the past month average value should be A124, A125, A126 models average duration.

the answer will be 432 + 322 + 100 / 3 = 854 = 284.5

Anyone know how to implement this average value during the past 1 month?

Thank you so much for your help!

Thanks,

Jean

1 ACCEPTED SOLUTION
Community Support

Hi @heejinyune ,

``Measure = CALCULATE(AVERAGE('Table'[Model Built Duration [Mins]]]),FILTER('Table',[Model Built Date]>=EDATE(TODAY(),-1)&&[Model Built Date]<=TODAY()))``

Note: today is 2022/11/14, so the average is :(322+100)/2 = 211

Final output:

Best Regards,

Jianbo Li

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

2 REPLIES 2
Community Support

Hi @heejinyune ,

``Measure = CALCULATE(AVERAGE('Table'[Model Built Duration [Mins]]]),FILTER('Table',[Model Built Date]>=EDATE(TODAY(),-1)&&[Model Built Date]<=TODAY()))``

Note: today is 2022/11/14, so the average is :(322+100)/2 = 211

Final output:

Best Regards,

Jianbo Li

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

Super User

@heejinyune Try:

``````Measure =
VAR __Date = MAX('Table'[Date])
VAR __Year = YEAR(__Date)
VAR __Month = MONTH(__Date)
VAR __Table = FILTER(ALL('Table'),YEAR([Date]) = __Year && MONTH([Date]) = __Month)
VAR __Return = AVERAGEX(__Table,[Model Built Duration [Mins])
RETURN
__Return``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors