Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
PoweredOut
Resolver I
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/YearHC RunningHC Running Avg.
Jan/2023100100
Feb/2023120110
Mar/2023115111,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
PoweredOut
Resolver I
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

PoweredOut_0-1683727066354.png

 

 

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 )

 

.  

View solution in original post

5 REPLIES 5
PoweredOut
Resolver I
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

PoweredOut_0-1683727066354.png

 

 

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
Solution Supplier
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.

Dhairya
Solution Supplier
Solution Supplier

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. 

 

PoweredOut_0-1683635199875.png

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.