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
