Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Average of Monthly Totals

Hello All, Have a calendar table and facts table linked by date. I have a date filter where dates are flagged as trailing 12 month and the dates are atrributed  to a monthyear column

I can visually show count(BillIDs) by month.  But what i want to show the trailing twelve month average of the monthly totals.

IN theory something like this

calculate(Avg(Jan Total+Feb Total+March Total+....), DateFilter="TTM")

Thanks for your help.

1 ACCEPTED SOLUTION
Employee

You can try an expression like this using your existing measure in place of [Your Measure].  If no measure, wrap the expression inside CALCULATE( ) and replace [Your Measure].

Twelve Month Avg =
CALCULATE (
AVERAGEX ( VALUES ( 'Calendar'[MonthYear] ), [Your Measure] ),
ALL ( 'Calendar' ),
'Calendar'[DateFilter] = "TTM"
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

2 REPLIES 2
Employee

You can try an expression like this using your existing measure in place of [Your Measure].  If no measure, wrap the expression inside CALCULATE( ) and replace [Your Measure].

Twelve Month Avg =
CALCULATE (
AVERAGEX ( VALUES ( 'Calendar'[MonthYear] ), [Your Measure] ),
ALL ( 'Calendar' ),
'Calendar'[DateFilter] = "TTM"
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Helper I

I guess i am confused on the VALUES ( 'Calendar'[MonthYear] )

I ahve 1 table called calendar with the MOnth year in it

I have another table called placements where I will do a measure called "total placements"=count(billid)

What do I put in the DAX again?

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors