Calculating YTD FTE by distinct employee ID

[loos]

 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

[Anonymous]

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.

[loos]

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?

[Anonymous]

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.

[loos]

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

[amitchandak]

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

[loos]

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