cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## Average of a running total

I have a Headcount Running Total Measure. Which is getting it's data from an employee history table. Meaning there could be more than one entry for an employee. This table has a relationship with the date table on the Employee Start Date.

``````Employee Running Total =
VAR MaxDate = MAX('DIM Date'[Date])
VAR MinDate = MIN('DIM Date'[Date])
RETURN
CALCULATE (
[Employee Base],
KEEPFILTERS('Employee'[ValidFromNew] <= MaxDate &&
'Employee'[ValidTo] >= MaxDate),
ALL('DIM Date'))``````

I have the requirement to find the Average of this number on speacific date. I need to have the result as below in red. Jan is divided by 1, Feb by 2 and so on.

 Month/Year HC Running HC Running Avg. Jan/2023 100 100 Feb/2023 120 110 Mar/2023 115 111,6

I have tried, but the result isn't what I want

``````Avg. Running Total per Month
AVERAGEX(
VALUES(
'DIM DATE'[Month/Year]),
[Employee Running Total])``````

Any ideas?

1 ACCEPTED SOLUTION
Resolver I

I have solved it with help from https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/ @marcorusso

i adapted it to fit my needs

``````Avg. Headcount =
VAR NumOfMonths = Max('DIM Date'[Month])
VAR LastCurrentDate =
MAX ( 'DIM Date'[Date] )
VAR Period =
DATESINPERIOD ( 'DIM Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX (
VALUES ( 'DIM Date'[Month-Year] ),
[Employee Running Total]
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, 'DIM Date'[Date] )
VAR LastDate = MAX ( 'Employee'[ValidFromNew])
RETURN
IF ( FirstDateInPeriod <= LastDate, Result )``````

.

5 REPLIES 5
Resolver I

I have solved it with help from https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/ @marcorusso

i adapted it to fit my needs

``````Avg. Headcount =
VAR NumOfMonths = Max('DIM Date'[Month])
VAR LastCurrentDate =
MAX ( 'DIM Date'[Date] )
VAR Period =
DATESINPERIOD ( 'DIM Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX (
VALUES ( 'DIM Date'[Month-Year] ),
[Employee Running Total]
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, 'DIM Date'[Date] )
VAR LastDate = MAX ( 'Employee'[ValidFromNew])
RETURN
IF ( FirstDateInPeriod <= LastDate, Result )``````

.

Solution Supplier

'YourTable'[DateColumn] & 'YourTable'[MonthColumn] implies Your Date dimension table.

'YourTable'[ValueColumn] implies your Fact Table from where numeric values you want to calculate.

Solution Supplier

Average Of Running Total =
DIVIDE (
SUMX (
VALUES ( 'YourTable'[DateColumn] ),
"RunningTotal",
CALCULATE (
SUM ( 'YourTable'[ValueColumn] ),
FILTER (
ALLSELECTED ( 'YourTable'[DateColumn] ),
'YourTable'[DateColumn] <= EARLIER ( 'YourTable'[DateColumn] )
)
)
),
[RunningTotal]
),
COUNTROWS ( VALUES ( 'YourTable'[MonthColumn] ) )
)

Please try this code.

Resolver I

Unfortunaetly it doesn't work. Context is Year = 2023. I want to be able to Show in March the average of Jan, Feb & Mar.

Measure I used.

``````Headcount  Avg. =
DIVIDE (
SUMX (
VALUES ( 'DIM Date'[Date]),
"RunningTotal",
CALCULATE (
DISTINCTCOUNT('Employee'[EmployeeID]),
FILTER (
ALLSELECTED ( 'DIM Date' ),
'DIM Date'[Date] <= EARLIER ('DIM Date'[Date])
)
)
),
[RunningTotal]
),
COUNTROWS ( VALUES ('DIM Date'[Period]  ) )
)``````

Resolver I

I have tried replacing "YourTable", but I am not getting the results I want.

When you mention Your Table, do you mean DIM Date or Employee?