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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

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

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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!!

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 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.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors