Need help with totalytd and periods - HR data

[Shynel]

Hi guys, I need help with my measure, that summarize FTE cumulative yearly.
I have seperate calendar to select period of time.

Measure FTE =
VAR _StartPeriod = FIRSTDATE('Calendar Periods'[Date])
VAR _EndPeriod = LASTDATE('Calendar Periods'[Date])
VAR _FTETable =
CALCULATETABLE(
SUMMARIZE(
Aggreements,
'Aggreements'[ID],
"AVGFTE",
// if there is more than one changes in agreement in a month I have to count FTE properly
AVERAGE(Aggreements[FTE])
),
// search active aggreements within selected period
Aggreements[EndDate] >= _StartPeriod,
Aggreements[StartDate] <= _EndPeriod
)
RETURN
SUMX(
_FTETable ,
[AVGFTE]
)

When I select all year in calendar slicer, the data are not summarize monthly. How should I change the measure above and group FTE montly?

[Shynel]

Hi @v-tianyich-msft , please take a look for my report PBIX FILE and sample data.

There is a measure that should sum up the average FTE for each month of the year.
January: 3,8
February: 3,8 + 3,8 = 7,6
March: 7,6 + 3,8 = 11,4
...
December: 50,3 - cumulative FTE for the whole year

Unfortunately it is not working today. I suspect that because I am counting the average for the period and not the total. Converting to a sum will result in multiplied values for the month (example ID 2).

[v-tianyich-msft]

Hi @Shynel ,

Because of the policy, I can't download your file, please show your sample data in a usable form as well as the expected results.

Best regards,
Community Support Team_ Scott Chang

[Shynel]

Hi @v-tianyich-msft ,

Measure FTE =

VAR _StartPeriod = FIRSTDATE('Calendar Periods'[Date])

VAR _EndPeriod = LASTDATE('Calendar Periods'[Date])

VAR _FTETable =

CALCULATETABLE(

    SUMMARIZE(

        Agreements,

        'Agreements'[ID],

        "AVGFTE",

        // if there is more than one changes in agreement in a month I have to count FTE properly

        AVERAGE(Agreements[FTE])

    ),

    // search active aggreements within selected period

    Agreements[EndDate] >= _StartPeriod,

    Agreements[StartDate] <= _EndPeriod

    )

RETURN

SUMX(

    _FTETable,

    [AVGFTE]

)

Measure FTE TotalYTD =

CALCULATE(

    [Measure FTE],

    DATESYTD( 'Calendar Periods'[Date])

)

[v-tianyich-msft]

Hi @Shynel ,

You can refer to the following example where I am accumulating Max as your measure:

Measure 2 = var _t = ADDCOLUMNS('Table',"A",SUMX(FILTER(ALL('Table'),[ID]<=EARLIER([ID])),[Max]))
RETURN SUMX(_t,[A])

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[v-tianyich-msft]

Hi @Shynel ,

Not sure about your data model, so I made simple samples just to show the screening process only, the specifics need to be modified by you:

Measure = var _min =MIN('Table 2'[Date])
var _max = MAX('Table 2'[Date])
RETURN CALCULATE(COUNTAX('Table',[ID]),FILTER('Table',[Start]<=_max&&[End]>=_min))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.