Help need with creating this new measure

Hey grkanth81 ,

Calculate your new measure based on the logic you've described.

Objective

You want a measure that:

• Uses cumulative hours up to each period.

• Divides that by the average of the FTE sum for:

  • Start period (always Jul-24, i.e. 202501).

  • And the current period.

Steps

For a given period:

• Numerator: cumulative sum of Disc_Hrs from Hours table up to and including the current period.

• Denominator: average of:

  • SUM(FTE) for start period (202501), and

  • SUM(FTE) for the current period.

The DAX Measure

CumulativeHrsAdjusted = 
VAR StartPeriod = 202501

VAR CurrentPeriod = MAX(Hours[period])

-- Cumulative Disc_Hrs up to the current period
VAR CumulativeHrs =
    CALCULATE(
        SUM(Hours[Disc_Hrs]),
        FILTER(
            ALL(Hours),
            Hours[period] <= CurrentPeriod
        )
    )

-- FTE for Start Period (Jul-24)
VAR StartPeriodFTE =
    CALCULATE(
        SUM(FTE[FTE]),
        FTE[period] = StartPeriod
    )

-- FTE for Current Period
VAR CurrentPeriodFTE =
    CALCULATE(
        SUM(FTE[FTE]),
        FTE[period] = CurrentPeriod
    )

-- Average of Start + Current FTE
VAR AvgFTE = DIVIDE(StartPeriodFTE + CurrentPeriodFTE, 2)

-- Final adjusted value
RETURN
DIVIDE(CumulativeHrs, AvgFTE)

Things to remember

• DIVIDE() is used to safely handle divide-by-zero cases.

• The measure uses MAX(Hours[period]) assuming you are slicing by Period and need the latest visible period.

• If you are slicing by a Period table instead, just replace MAX(Hours[period]) with MAX(Period[Period_integer]).

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

Hi grkanth81 

Try the below DAX:

CumulativeHrsDivByAvgFTE =

VAR CumHrs =

CALCULATE (

SUM ( Hours[Disc_Hrs] ),

FILTER (

ALLSELECTED ( 'Period'[Period_integer] ),

'Period'[Period_integer] <= MAX ( 'Period'[Period_integer] )

)

)

VAR StartFTE =

CALCULATE (

SUM ( FTE[FTE] ),

FTE[period] = 202501

)

VAR CurrentFTE =

CALCULATE (

SUM ( FTE[FTE] ),

FTE[period] = MAX ( 'Period'[Period_integer] )

)

VAR AvgFTE = DIVIDE( StartFTE + CurrentFTE, 2 )

RETURN

DIVIDE( CumHrs, AvgFTE )