Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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?
Solved! Go to Solution.
Hi, @Anonymous
Sorry, I forgot to consider the null in the ValidUntil column.
please check the below amended measure.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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.
Hi, @Anonymous
Sorry, I forgot to consider the null in the ValidUntil column.
please check the below amended measure.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
107 | |
68 | |
48 | |
44 | |
44 |