Help need with creating this new measure

[grkanth81]

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	1/07/2023	31/07/2023
202402	FY 2024	Aug-23	1/08/2023	31/08/2023
202403	FY 2024	Sep-23	1/09/2023	30/09/2023
202404	FY 2024	Oct-23	1/10/2023	31/10/2023
202405	FY 2024	Nov-23	1/11/2023	30/11/2023
202406	FY 2024	Dec-23	1/12/2023	31/12/2023
202407	FY 2024	Jan-24	1/01/2024	31/01/2024
202408	FY 2024	Feb-24	1/02/2024	29/02/2024
202409	FY 2024	Mar-24	1/03/2024	31/03/2024
202410	FY 2024	Apr-24	1/04/2024	30/04/2024
202411	FY 2024	May-24	1/05/2024	31/05/2024
202412	FY 2024	Jun-24	1/06/2024	30/06/2024
202501	FY 2025	Jul-24	1/07/2024	31/07/2024
202502	FY 2025	Aug-24	1/08/2024	31/08/2024
202503	FY 2025	Sep-24	1/09/2024	30/09/2024
202504	FY 2025	Oct-24	1/10/2024	31/10/2024
202505	FY 2025	Nov-24	1/11/2024	30/11/2024
202506	FY 2025	Dec-24	1/12/2024	31/12/2024
202507	FY 2025	Jan-25	1/01/2025	31/01/2025
202508	FY 2025	Feb-25	1/02/2025	28/02/2025
202509	FY 2025	Mar-25	1/03/2025	31/03/2025
202510	FY 2025	Apr-25	1/04/2025	30/04/2025

FTE table:

period	FE code	FTE
202501	0001	200
202501	0002	220
202501	0003	180
202502	0001	170
202502	0002	230
202502	0003	220
202503	0001	190
202503	0002	210
202503	0003	210
202504	0001	210
202504	0002	220
202504	0003	160
202505	0001	150
202505	0002	250
202505	0003	135
202506	0001	175
202506	0002	215
202506	0003	195
202507	0001	220
202507	0002	215
202507	0003	200

Hours table:

period	FE code	Disc_Hrs
202501	0001	600
202501	0002	500
202501	0003	700
202502	0001	500
202502	0002	600
202502	0003	650
202503	0001	450
202503	0002	500
202503	0003	390
202504	0001	450
202504	0002	550
202504	0003	625
202505	0001	500
202505	0002	525
202505	0003	670
202506	0001	635
202506	0002	475
202506	0003	590
202507	0001	585
202507	0002	655
202507	0003	490

I have created a new measure to get the Cumulative Hours by period as below:
CumulativeHrs = CALCULATE (
SUM ( Hours[Disc_Hrs] ),
FILTER (
ALLSELECTED ( Hours),
Hours[period]<= MAX (Hours[period] )
)
)

Now I would like to create another new measure which should be as per the below calculation:

For Jul2024 the calculation should be CumulativeHrsforJul2024/( (sum of FTE of the start period (Jul2024))/2)

For Aug2024 the calculation should be CumulativeHrsforAug2024/( (sum of FTE of the start period (Jul2024) + sum of FTE of the Aug2024)/2)

For Sep2024 the calculation should be CumulativeHrsforSep2024/( (sum of FTE of the start period (Jul2024) + sum of FTE of the Sep2024)/2)

For Oct2024 the calculation should be CumulativeHrsforOct2024/( (sum of FTE of the start period (Jul2024) + sum of FTE of the Oct2024)/2)

and so on...

Can someone help with creating a new measure as per the above calculation?

Thanks,,

grkanth81

[v-karpurapud · Accepted solution]

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.

[grkanth81]

Thanks karpurapud. I am looking into it. But you mentioned in your post you have attached pbix file but I couldn't find it. can you please attach it so I can refer to it ?

[v-karpurapud]

Hi @grkanth81 

I apologize . I might have missed . I have attached now . Please have a look into it.

[v-karpurapud]

Hi @grkanth81 

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank You!

[grkanth81]

yes. Thanks a lot karpurapud. Your solution worked perfectly and sorry for late reply.

[Nasif_Azam]

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

[ABD128]

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 )