Average of a running total

[PoweredOut]

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?

[PoweredOut · Accepted 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 )

.  

[Dhairya]

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

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

[Dhairya]

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.

[PoweredOut]

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]  ) )
)

[PoweredOut]

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?