Calculate FTE with gaps in dates and the dates are in different rows.

[Anonymous]

Hi,

I am trying to calculate the FTE per month per Department/ Devision and my data looks like this:

As you can see for employee# 6042 He had different FTE status through time.

From 15-10-2018 till 01-09-2019 it was 1.0 FTE

From 01-09-2019 till 01-01-2022 it was 0.9 FTE

From 01-01-2022 till 01-07-2022 it was 1.0 FTE

From 01-07-2022 till Today is is 0.9 FTE

And I want my output to be something like this but correct, with the gaps filled between two dates that are in different rows. So for Employee 6042 1 FTE for January, February, March, April, May and June and 0.90 FTE for July and the rest of the year. So that the column totals per month (or year or quarter) make sense...

Can anyone help me with this?

[Anonymous]

I didn't know how to add the pbix file to this post so I've created a onedrive link:

OneDrive Link to .pbix and data sheet 

Hope this helps!

[Anonymous]

Hi @Anonymous ,

Do you mean to fill all null values in the matrix, the value of the null value depends on the value corresponding to the previous non-null month as below screenshot shown.

Please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Anonymous]

Here is the link to the .pbix and datasheet: OneDrive Link 

[Anonymous]

Hi @Anonymous ,

I don't have permission to get pbix.

Please refer to:

How to provide sample data in the Power BI Forum

You can refer to the following posts that may be helpful to you:

Solved: Fill blank values with previous value - Microsoft Power BI Community

Solved: Fill blanks with previous value - Microsoft Power BI Community

dataonwheels.wordpress.com

Best Regards,

Neeko Tang

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

[Anonymous]

Hi Neeko,

There are no blanks in the dataset. The problem is that there are multiple rows after each change in hours. For example Employee # 6042 with 4 rows on different FTE dates:

From 15-10-2018 till 01-09-2019 it was 1.0 FTE

From 01-09-2019 till 01-01-2022 it was 0.9 FTE

From 01-01-2022 till 01-07-2022 it was 1.0 FTE

From 01-07-2022 till Today is is 0.9 FTE

Can you work with this sample data?

The link to the files is: https://1drv.ms/u/s!AnfNpx_Gnyu2gYUyzh_MW2oTRo-epQ?e=fFkW4Z 

Employee #	FTE Date	FTE Status	Employment Status	Division	Department	Location	Job Title	Hire Date	Contract End
6019	01-01-22	0,8	Contractor	Operations	Legal	Netherlands	Job Title Employee 6019	01-01-18
6024	09-07-18	1	Employee	Product Development	Engineering	Netherlands	Job Title Employee 6024	09-07-18
6025	01-07-18	1	Employee	Management Team	Management Team	Netherlands	Job Title Employee 6025	01-07-18
6035	15-09-18	1	Employee	Customer Success Organization	Pre Sales	Germany	Job Title Employee 6035	15-09-18
6035	01-04-22	0,8	Employee	Customer Success Organization	Pre Sales	Germany	Job Title Employee 6035	15-09-18
6036	01-01-22	0,8	Employee	Product Development	Product Operations	Netherlands	Job Title Employee 6036	01-09-18
6040	24-09-18	1	Employee	Sales	Direct Sales	United States	Job Title Employee 6040	24-09-18
6041	01-10-18	0,9	Employee	Product Development	IT & Security	Netherlands	Job Title Employee 6041	01-10-18
6042	15-10-18	1	Employee	Operations	Human Resources	Netherlands	Job Title Employee 6042	15-10-18
6042	01-09-19	0,9	Employee	Operations	Human Resources	Netherlands	Job Title Employee 6042	15-10-18
6042	01-01-22	1	Employee	Operations	Human Resources	Netherlands	Job Title Employee 6042	15-10-18
6042	01-07-22	0,9	Employee	Operations	Human Resources	Netherlands	Job Title Employee 6042	15-10-18
6043	29-10-18	1	Employee	Sales	Direct Sales	Netherlands	Job Title Employee 6043	29-10-18
6045	05-11-18	1	Employee	Sales	Inside Sales	Germany	Job Title Employee 6045	05-11-18
6046	11-12-18	0,8	Employee	Product Development	Engineering	Netherlands	Job Title Employee 6046	11-12-18
6047	01-02-19	1	Employee	Product Development	Engineering	Netherlands	Job Title Employee 6047	01-02-19
6047	01-04-21	0,95	Employee	Product Development	Engineering	Netherlands	Job Title Employee 6047	01-02-19
6048	01-02-19	0,9	Employee	Product Development	IT & Security	Netherlands	Job Title Employee 6048	01-02-19
6050	09-02-19	1	Employee	Customer Success Organization	Consultancy	Canada	Job Title Employee 6050	09-02-19
6051	04-03-19	1	Employee	Sales	Inside Sales	United Kingdom	Job Title Employee 6051	04-03-19
6058	01-07-19	1	Employee	Customer Success Organization	Consultancy	Netherlands	Job Title Employee 6058	01-07-19
6059	01-11-18	1	Contractor	Product Development	Engineering	Netherlands	Job Title Employee 6059	11-10-21
6059	01-03-20	0,8	Contractor	Product Development	Engineering	Netherlands	Job Title Employee 6059	11-10-21

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) We can create tables.

Table = 
var _date=FILTER(CALENDAR(EOMONTH(MIN('FTE Report'[FTE Date]),-1)+1,TODAY()),DAY([Date])=1)
return
CROSSJOIN(SELECTCOLUMNS('FTE Report',"Employee#",[Employee #],"FTE Date", [FTE Date],"Value",[FTE Status]),_date)

Table22 = 
DISTINCT(FILTER('Table',[Flag]=1))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Anonymous]

Thank you so much, wouldn't have got this by my self