cancel
Showing results for
Did you mean:
New Member

## Advanced DAX help - Rolling 12 month average of another measure

Hi Guys,

I am hoping a DAX guru can help with a complicated DAX problem that I have.

I initially started using the rolling 12 month template provided by the sqlbi guys (https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/) but had to deviate substantially due to the differences in the underlying data and additional requirements around visualisation filters.

The goal

Goal is to produce a rolling 12 month average of an 'average transaction value per customer' per account_id

Complications

Unfortunately the data is structured like this:

 Account_ID System All Flows (\$) Net Membership Year_Month 1 A 1,000,000 Sep 21 1 S 50,000 Sep 21 1 null 5000 Sep 21 2 A 30,000 Sep 21 2 S 5,000 Sep 21 2 null 300 Sep 21

Measure 1 - Total \$ spent - sum(All Flows)

Measure 2 - Total Customers - sum(Net Membership)

Measure 3 - divide( [Total \$ spent], [Total Customers])

What I need is to have an average of measure 3 over 12 months per account ID

Visualisation requirements

The default view is all companies. However users can click on individiual companies to filter only for that companies' details.

The user can also multi select different companies (i.e. select company 2, 6, 😎 and the measure needs to calculate correctly.

There are a myriad of other filters based on location, sales manager for each company which isn't shown above but are referrenced in other dimension tables.

Other requirements

I could probably 'cheat' by creating a table that pre-calculates the value for each employer. However due to interactions with other measures, this isn't preferred. Also to de-clutter the PBI file (and also challenge my DAX understanding) I would like to do all this within a measure.

Where i got to so far

``````All flows per Mbr Rolling 12m =

VAR LastCurrentDate = LASTDATE('f Combined Facts'[YEAR_MONTH])
var LastYearDate = DATEADD(LASTDATE('f Combined Facts'[YEAR_MONTH]),-1,YEAR)
var HasOneID = HASONEVALUE('f Combined Facts'[ACCOUNT_ID])

var tbl = SUMMARIZE( filter('f Combined Facts', 'f Combined Facts'[YEAR_MONTH] > LastYearDate && 'f Combined Facts'[YEAR_MONTH] <= LastCurrentDate),
'd Account Managed Employers'[ACCOUNT_ID],
'f Combined Facts'[YEAR_MONTH],
"All Flows", sum('f Combined Facts'[All flows]),
"Net Mbrs", sum('f Combined Facts'[Net Membership (Agg)]),
"AvgFlow", if(
ISBLANK(
divide(sum('f Combined Facts'[All flows]),sum('f Combined Facts'[Net Membership (Agg)]))
),0, divide(sum('f Combined Facts'[All flows]),sum('f Combined Facts'[Net Membership (Agg)])))

)

VAR SingleEmployer =
calculate(sumx(tbl, [AvgFlow]))

var AllEmpnum = sumx(tbl, [All Flows])
var AllEmpD = sumx(tbl,[Net Mbrs])

var Result = if(HasOneID,SingleEmployer,divide(AllEmpnum,AllEmpD))

RETURN Result``````

What I'm struggling to do

The variable table seems to be calculated/created correctly on the month level, but i'm struggling to 'take a step back' and aggregate at a rolling 12 month level for this calculation. The initial sqlbi help article has the monthly values 'materialised' and then uses datesinperiod to filter down to the relevant 12 month period. I'm not sure how this can be done within a single measure.

Am grateful for any guidance.

Many thanks

Super User

Think like the Grand Total. Very often measures designed for the Grand Total will also work for the Row and Column Totals and for the individual cells.

In your case calculate for a group of accounts.

Provide some more sample data that covers the issue better  (more than 12 months, for example) if you like more help.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.