Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
loos
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 IDFTEDate
John11110.509/1/2020
Jane22221.009/1/2020
Tom33330.809/1/2020
John11110.5010/1/2020
Jane22221.0010/1/2020
Tom33330.8010/1/2020
Mark44441.0010/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
v-rongtiep-msft
Community Support
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
    )
)

 

vpollymsft_0-1664242994889.pngvpollymsft_1-1664243099640.png

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.

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?

 

v-rongtiep-msft
Community Support
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] ) )
)

 

vpollymsft_0-1664169408814.png

 

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.

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 

 

EmployeeEmployee IDFTEMonthCompany
John11110.509/1/2020A
Jane22221.009/1/2020A
Tom33330.809/1/2020B
John11110.5010/1/2020A
Jane22221.0010/1/2020A
Tom33330.8010/1/2020B
Mark44441.0010/1/2020B
Jane22221.0011/1/2020A
Tom33330.8011/1/2020B
Mark44441.0011/1/2020B
Jane22221.0012/1/2020A
Tom33330.8012/1/2020B
Mark44441.0012/1/2020B
Leon55551.0012/1/2020A

 

amitchandak
Super User
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]) )

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 

 

EmployeeEmployee IDFTEMonthCompany
John11110.509/1/2020A
Jane22221.009/1/2020A
Tom33330.809/1/2020B
John11110.5010/1/2020A
Jane22221.0010/1/2020A
Tom33330.8010/1/2020B
Mark44441.0010/1/2020B
Jane22221.0011/1/2020A
Tom33330.8011/1/2020B
Mark44441.0011/1/2020B
Jane22221.0012/1/2020A
Tom33330.8012/1/2020B
Mark44441.0012/1/2020B
Leon55551.0012/1/2020A

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.