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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
Anonymous
Not applicable

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.

 

CompanyEmployeeIDFTEValidfromValiduntil
A001101-01-201930-06-2019
A0010,801-07-201931-12-2019
A0010,801-01-2020null

 

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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] )
))


Microsoft MVP



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.




LinkedInVisit my LinkedIn page




Outlook BookingSchedule a short Teams meeting to discuss your question





View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@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
Super User
Super User

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] )
))


Microsoft MVP



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.




LinkedInVisit my LinkedIn page




Outlook BookingSchedule a short Teams meeting to discuss your question





Jihwan_Kim
Super User
Super User

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.
Picture1.png

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



Microsoft MVP



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.




LinkedInVisit my LinkedIn page




Outlook BookingSchedule a short Teams meeting to discuss your question





amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.