Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to Solution.
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 )
.
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 )
.
'YourTable'[DateColumn] & 'YourTable'[MonthColumn] implies Your Date dimension table.
'YourTable'[ValueColumn] implies your Fact Table from where numeric values you want to calculate.
Average Of Running Total =
DIVIDE (
SUMX (
ADDCOLUMNS (
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.
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 (
ADDCOLUMNS (
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] ) )
)
Thanks for replying
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?
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |