cancel
Showing results for
Did you mean:

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

New Member

## Need help with totalytd and periods - HR data

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?

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
New Member

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).

Community Support

Hi @Shynel ,

Best regards,
Community Support Team_ Scott Chang

New Member

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])
)

Community Support

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.

Community Support

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors