Forum Discussion
Monthly output without reference date
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?
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] )))
4 Replies
Anonymous , Please refer my HR blog can help, on the same topic
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
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!!
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] )))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.
