Forum Discussion

grkanth81's avatar
grkanth81
Icon for Helper II rankHelper II
1 year ago
Solved

Help need with creating this new measure

Hi all, We are using Power BI Report Server (May 2025 version).  Here is my sample test data: Period table: Period_integer FY Period per_begin_date per_end_date 202401 FY 2024 Jul-23 ...
Power BI Report Server (May 2025 version)
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hi grkanth81 


    Welcome to the Microsoft Fabric Community Forum. Also, thank you ABD128 and Nasif_Azam for your quick responses.

     

    To calculate the required metric, follow the instructions where the cumulative hours for each month are divided by the average of the FTE from the start period (Jul-2024) and the FTE from the current period.

     

    Before creating the measure, ensure that the following relationships exist in the data model:

    • Period[Period_integer] → FTE[period]
    • Period[Period_integer] → Hours[period]


    Consider the below DAX:

     

    CumulativeHrsDivByAvgFTE = 
VAR CurrentPeriod = MAX('Period'[Period_integer])

VAR CumHrs =
    CALCULATE (
        SUM ( Hours[Disc_Hrs] ),
        FILTER (
            ALL ( 'Period' ),
            'Period'[Period_integer] <= CurrentPeriod
        )
    )

VAR StartFTE =
    CALCULATE (
        SUM ( FTE[FTE] ),
        FILTER (
            ALL ( FTE ),
            FTE[period] = 202501
        )
    )

VAR CurrentFTE =
    CALCULATE (
        SUM ( FTE[FTE] ),
        FILTER (
            ALL ( FTE ),
            FTE[period] = CurrentPeriod
        )
    )
VAR AvgFTE = DIVIDE ( StartFTE + CurrentFTE, 2 )
RETURN
    DIVIDE ( CumHrs, AvgFTE )

    Please refer attached Screenshot and .pbix file  for your reference and share your thoughts.


    If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.

    Welcome to the Microsoft Fabric Forum. Also, thank you  and  for your qucik responses.

     

    If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

    Thank you for being part of Fabric Community Forum.

    Regards,
    Karpurapu D,
    Microsoft Fabric Community Support Team.

     

     



Nasif_Azam's avatar
Nasif_Azam
Icon for Super User rankSuper User
1 year ago

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