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.

Regular Visitor

## Calculating YTD FTE by distinct employee ID

Hello! I am trying to calculate the YTD FTE (a number given to each employee based on their employment, a FT employee gets 1.00, a PT .50 and so on) but right now I am getting each month added to the previous months.

SUMFTE = CALCULATE(SUMX(values(Crosstab[FTE]),DISTINCTCOUNT(Crosstab[ID])))

YTD = CALCULATE([SUMFTE],DATESYTD(DimDate[Date]))

 Employee ID FTE Date John 1111 0.50 9/1/2020 Jane 2222 1.00 9/1/2020 Tom 3333 0.80 9/1/2020 John 1111 0.50 10/1/2020 Jane 2222 1.00 10/1/2020 Tom 3333 0.80 10/1/2020 Mark 4444 1.00 10/1/2020

Per this sample data, YTD for 9/2020 should be 2.3 but then YTD for 10/2020 should be 3.3.

https://www.dropbox.com/t/NatTaGSdtWrLZPPQ

6 REPLIES 6
Community Support

Hi @loos ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 measures.

``````Measure =
VAR _earliertime =
CALCULATE (
MIN ( 'Table'[Month] ),
FILTER (
ALL ( 'Table' ),
'Table'[Employee ID] = SELECTEDVALUE ( 'Table'[Employee ID] )
)
)
VAR _thevalue =
CALCULATE (
MAX ( 'Table'[FTE] ),
FILTER (
ALL ( 'Table' ),
'Table'[Month] = _earliertime
&& 'Table'[Employee ID] = SELECTEDVALUE ( 'Table'[Employee ID] )
)
)
RETURN
IF ( MAX ( 'Table'[Month] ) = _earliertime, 1, 0 )
``````
``````result =
CALCULATE (
SUM ( 'Table'[FTE] ),
FILTER (
ALL ( 'Table' ),
'Table'[Month] <= SELECTEDVALUE ( 'Table'[Month] )
&& [Measure] = 1
)
)
``````

I also created 2 columns. Please refer to it.

Best Regards

Community Support Team _ Polly

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

Regular Visitor

Hello! So I cannot make the result column in my table . I have to override the table every month with new data through excel ( it is a three year rolling data) and I get an error if there is a newly made column that is not made through powerquery. I normally add columns and merge via Powerquery. How can do this in an alternative way?

Community Support

Hi @loos ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

``````measure =
CALCULATE (
SUM ( 'Table'[FTE] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) )
)
``````

Or a column.

``````Column =
CALCULATE (
SUM ( 'Table'[FTE] ),
FILTER ( ( 'Table' ), 'Table'[Date] = EARLIER ( 'Table'[Date] ) )
)
``````

Best Regards

Community Support Team _ Polly

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

Regular Visitor

Hello! Thank you so much for helping me look into this.

Unfortunately I tried the solutions but I am not getting the right numbers.

The answer should be 4.3 for without the Month, Year or Company filtered.

Here is a sample:

 Employee Employee ID FTE Month Company John 1111 0.50 9/1/2020 A Jane 2222 1.00 9/1/2020 A Tom 3333 0.80 9/1/2020 B John 1111 0.50 10/1/2020 A Jane 2222 1.00 10/1/2020 A Tom 3333 0.80 10/1/2020 B Mark 4444 1.00 10/1/2020 B Jane 2222 1.00 11/1/2020 A Tom 3333 0.80 11/1/2020 B Mark 4444 1.00 11/1/2020 B Jane 2222 1.00 12/1/2020 A Tom 3333 0.80 12/1/2020 B Mark 4444 1.00 12/1/2020 B Leon 5555 1.00 12/1/2020 A

Super User

@loos , That will come when you remove employee and ID from visual or remove filters

YTD = CALCULATE([SUMFTE],DATESYTD(DimDate[Date]), allselected(Table[Employee], Table[ID]) )

Regular Visitor

Hello! Thank you so much for helping me look into this.

Unfortunately I tried the solutions but I am not getting the right numbers.

The answer should be 4.3 for without the Month, Year or Company filtered.

Here is a sample:

 Employee Employee ID FTE Month Company John 1111 0.50 9/1/2020 A Jane 2222 1.00 9/1/2020 A Tom 3333 0.80 9/1/2020 B John 1111 0.50 10/1/2020 A Jane 2222 1.00 10/1/2020 A Tom 3333 0.80 10/1/2020 B Mark 4444 1.00 10/1/2020 B Jane 2222 1.00 11/1/2020 A Tom 3333 0.80 11/1/2020 B Mark 4444 1.00 11/1/2020 B Jane 2222 1.00 12/1/2020 A Tom 3333 0.80 12/1/2020 B Mark 4444 1.00 12/1/2020 B Leon 5555 1.00 12/1/2020 A