topic Re: Calculating YTD FTE by distinct employee ID in DAX Commands and Tips

Calculating YTD FTE by distinct employee ID

loos — Mon, 26 Sep 2022 16:01:41 GMT

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

Re: Calculating YTD FTE by distinct employee ID

amitchandak — Sat, 24 Sep 2022 02:28:50 GMT

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

Re: Calculating YTD FTE by distinct employee ID

Anonymous — Mon, 26 Sep 2022 05:17:55 GMT

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

If I have misunderstood your meaning, please provide more details with your desired output.

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.

Re: Calculating YTD FTE by distinct employee ID

loos — Mon, 26 Sep 2022 16:02:28 GMT

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:

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

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

Re: Calculating YTD FTE by distinct employee ID

loos — Mon, 26 Sep 2022 16:02:53 GMT

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:

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

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

Re: Calculating YTD FTE by distinct employee ID

Anonymous — Tue, 27 Sep 2022 01:51:19 GMT

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.

Re: Calculating YTD FTE by distinct employee ID

loos — Wed, 05 Oct 2022 12:19:43 GMT

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?