Monthly output without reference date

[Anonymous]

I want to create a matrix visual or line chart where I can show the development of the FTE (or something else) on a monthly base. Problem is that my dataset doesn't have a reference date column, but it has two columns validfrom and validuntil, like below.

Company	EmployeeID	FTE	Validfrom	Validuntil
A	001	1	01-01-2019	30-06-2019
A	001	0,8	01-07-2019	31-12-2019
A	001	0,8	01-01-2020	null

Which measure can I use too show the values SUM(FTE) on a monthly base? 

[Anonymous]

@Anonymous 

To achieve your required output, you must create new table with calendar and record FTE value for each date.

With current records it isnot possible get sum for each month. For example, the FTE value is 1 for Jan 2019 to June 2019, but we cannot identify whether it means 1 FTE for each month or 1 FTE for whole 6 months. If it is the latter, there is no criteria to distinguish the 1 FTE to 6 month, proportional or evenly?

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Sorry, I forgot to consider the null in the ValidUntil column.

please check the below amended measure.

SUM FTE =

CALCULATE (

CALCULATE(SUM( Data[FTE]), FILTER(Data, Data[Validuntil] <> BLANK())),

FILTER(Data, Data[Validfrom] <= MAX ( dates[Date] ) && Data[Validuntil] >= MIN(dates[Date])

)) + CALCULATE( CALCULATE(SUM( Data[FTE]), FILTER(Data, Data[Validuntil] = BLANK())),

FILTER(Data, Data[Validfrom] <= MAX ( dates[Date] )

))

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if wrongly understood what you need.

I assume that you have the custom-date-table.

By using this custom-date-table, no connection is needed at this moment, and please try to write the below.

SUM FTE =

CALCULATE (

SUM ( Data[FTE] ),

FILTER ( VALUES ( Data[Validfrom] ), Data[Validfrom] <= MAX ( dates[Date] ) ),

FILTER ( VALUES ( Data[Validuntil] ), Data[Validuntil] >= MIN ( dates[Date] ) )

)

By using the sample data that you provided, I come up with the below table visualization.

please let me know if I misunderstood.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[amitchandak]

@Anonymous , Please refer my HR blog can help, on the same topic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

or refer How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785