Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello all,
I was searching if there was something similar but did not find something for all the requirements I have.
Explaining the issue:
Data available
- I have one table with a list of activities, start date, end date, corresponding FTEs, among others
Example:
Activity | Start Date | End Date | FTEs |
Activity 1 | 15 Dec 2020 | 19 Jun 2021 | 50 |
The FTEs column corresponds to the FTEs for the whole duration of the Activity
- I have a separate date table with all the days, having a column mentioning if it is a weekend or not
Desired output
- I need a way to calculate the monthly, yearly (even daily) values of FTEs. For this, I should consider that the number of FTEs should be divided by time according to working days, not calendar days (ignore holidays, consider weekdays only).
Example: if I want to look at a month view, I would have for the activity presented as below
Month | FTEs |
Dec 2020 | 28.26* |
Jan 2021 | 50.00 |
Feb 2021 | 50.00 |
Mar 2021 | 50.00 |
Apr 2021 | 50.00 |
May 2021 | 50.00 |
Jun 2021 | 31.82* |
*1 Value adjusted via working days
[Working days for the activity in Dec 2020 (13) / Working days for Dec 2020 (23)]*FTEs (50) = 28.26
*2 same logic as *1, but considering the interval between 01 Jun 2021 and 19 Jun 2021: (14/22)*50 = 31.82
Basically, each time the date interval intersects the end or start of the activity, the way we do the calculation changes slightly. It always considers the ratio of Working days of the activity vs the number of working days of the interval in analysis.
If I look at this same activity from a year perspective, I should have:
Year | FTEs |
2020 | 2.48 |
2021 | 23.18 |
But image we were talking about the same activity but with the dates 15 Dec 2020 to 19 Jun 2022. In this case, it would be:
Year | FTEs |
2020 | 2.48 |
2021 | 50 |
2022 | 23.08 |
This is the same logic as before, but for the year instead of the months.
What has been done so far
Consider we have the following activities:
The monthly view should be something like:
BUT showing all the months anyway.
The measure I created seemed to be working for some limited number of rows – then I start having some memory issues and I could not totally compare if the actuals would work properly.
Measure =
VAR _FirstDate =
FIRSTDATE ( 'Date'[Date] )
VAR _LastDate =
LASTDATE ( 'Date'[Date] )
RETURN
SUMX (
FACTS,
CALCULATE (
SUM ( FACTS[Result gross (FTE)] )
* DIVIDE (
CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
SUM ( FACTS[Start date] ),
SUM ( FACTS[Finish date] )
),
'Date'[Weekend] = FALSE ()
),
CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN ( 'Date'[Date], _FirstDate, _LastDate ),
'Date'[Weekend] = FALSE ()
),
0
),
FACTS[Start date] <= _LastDate,
FACTS[Finish date] >= _FirstDate
)
)
Help would be much appreciated.
Thank you all for your help,
Pedro
Solved! Go to Solution.
@Anonymous ,
Here's a version that does not use CALCULATE and should be MUCH faster. Have a good look at its mechanics and if it does not work on the first attempt, do not panic, just try to understand how it works and adjust it accordingly. But beware of CALCULATE that's executed row by row on a fact table. YOU SHOULD NEVER DO IT AS IT'LL ALWAYS KILL PERFORMANCE.
Total FTEs =
// Dates must NOT be connected to Facts
// and must be marked as a Date table
VAR __firstDate = MIN( 'Dates'[Date] )
VAR _lastDate = MAX( 'Dates'[Date] )
// assuming that WorkingDay is 1 for
// a working day and 0 for a weekend
var __workingDayCount = SUM( 'Dates'[WorkingDay] )
RETURN
if( __workingDayCount > 0,
SUMX(
FILTER(
FACTS,
// getting only the rows where
// there is a non-empty overlap
// between (start, end) and
// (firstDate, lastDate)
Facts[Start date] <= __lastDate
&&
Facts[Finish date] >= __firstDate
),
// for each of the above rows calculate
// the percentage of FTE
var __fte = FACTS[Result gross (FTE)]
var __lowerDate =
MAX(
__firstDate,
Facts[Start date]
)
var __upperDate =
MIN(
__lastDate,
Facts[Finish date]
)
var __activityWorkingDayCount =
SUMX(
filter(
// We don't have to use
// ALL( Dates ) here due
// to the nature of the
// problem.
Dates,
and(
__lowerDate <= Dates[Date],
Dates[Date] <= __upperDate
)
),
Dates[WorkingDay]
)
return
// do not use DIVIDE here as it
// does nothing more than what
// I've put in here and in fact
// it slows down calculations
__fte * __activityWorkingDayCount
/ __workingDayCount
)
)
@Anonymous ,
Here's a version that does not use CALCULATE and should be MUCH faster. Have a good look at its mechanics and if it does not work on the first attempt, do not panic, just try to understand how it works and adjust it accordingly. But beware of CALCULATE that's executed row by row on a fact table. YOU SHOULD NEVER DO IT AS IT'LL ALWAYS KILL PERFORMANCE.
Total FTEs =
// Dates must NOT be connected to Facts
// and must be marked as a Date table
VAR __firstDate = MIN( 'Dates'[Date] )
VAR _lastDate = MAX( 'Dates'[Date] )
// assuming that WorkingDay is 1 for
// a working day and 0 for a weekend
var __workingDayCount = SUM( 'Dates'[WorkingDay] )
RETURN
if( __workingDayCount > 0,
SUMX(
FILTER(
FACTS,
// getting only the rows where
// there is a non-empty overlap
// between (start, end) and
// (firstDate, lastDate)
Facts[Start date] <= __lastDate
&&
Facts[Finish date] >= __firstDate
),
// for each of the above rows calculate
// the percentage of FTE
var __fte = FACTS[Result gross (FTE)]
var __lowerDate =
MAX(
__firstDate,
Facts[Start date]
)
var __upperDate =
MIN(
__lastDate,
Facts[Finish date]
)
var __activityWorkingDayCount =
SUMX(
filter(
// We don't have to use
// ALL( Dates ) here due
// to the nature of the
// problem.
Dates,
and(
__lowerDate <= Dates[Date],
Dates[Date] <= __upperDate
)
),
Dates[WorkingDay]
)
return
// do not use DIVIDE here as it
// does nothing more than what
// I've put in here and in fact
// it slows down calculations
__fte * __activityWorkingDayCount
/ __workingDayCount
)
)
Hi,
Thank you for the great solution! I have tried to apply it for headcount measure but when for the each employee it counts it well, total number is super weird.
Thanks a lot @daxer-almighty ! Worked perfectly! It would be better if it is even faster but I guess with this amount of data it is not easy to handle 😋
Thanks for all the inputs!
Hello @daxer-almighty ,
I though I had answered, sorry for the delay.
So basically, concerning the timings, it is a little bit slow for some visuals. The ones presented below are going at the month level from 1 Jan 2020 to 30 Jun 2022.
If I try to drill down (at day level) - not a must have but a nice to have - it obviously takes much more time.
I am not able to share the pbix directly because of the data it contains. The times I sent are for a "simplified" version of the DM (only three tables).
Not sure if it helps but please consider:
- 1 date Table
Date = ADDCOLUMNS(CALENDAR(DATE(2000,1,1),DATE(2100,12,31)),
"Year",YEAR([Date]),
"Month",MONTH([Date]),
"Day",DAY([Date]),
"Workday",SWITCH(WEEKDAY([Date]),1,0,7,0,1))
- 1 fact Table
- 1 Dimension Table
Based on the FACT table
Then added:
The two tables are connected via Base + Forecast ONB.
Measure is very similar to what you stated:
GrossFTEs =
// Dates must NOT be connected to Facts
// and must be marked as a Date table
VAR __firstDate = MIN( 'Date'[Date] )
VAR __lastDate = MAX( 'Date'[Date] )
// assuming that WorkingDay is 1 for
// a working day and 0 for a weekend
var __workingDayCount = SUM( 'Date'[Workday] )
RETURN
if( __workingDayCount > 0,
SUMX(
FILTER(
'Fact',
// getting only the rows where
// there is a non-empty overlap
// between (start, end) and
// (firstDate, lastDate)
'Fact'[From] <= __lastDate
&&
'Fact'[To] >= __firstDate
&&
'Fact'[Value Type] = "2"
),
// for each of the above rows calculate
// the percentage of FTE
var __ftegross = 'Fact'[Value]
var __lowerDate =
MAX(
__firstDate,
'Fact'[From]
)
var __upperDate =
MIN(
__lastDate,
'Fact'[To]
)
var __activityWorkingDayCount =
SUMX(
filter(
// We don't have to use
// ALL( Dates ) here due
// to the nature of the
// problem.
'Date',
and(
__lowerDate <= 'Date'[Date],
'Date'[Date] <= __upperDate
)
),
'Date'[Workday]
)
return
// do not use DIVIDE here as it
// does nothing more than what
// I've put in here and in fact
// it slows down calculations
__ftegross * __activityWorkingDayCount
/ __workingDayCount
)
)
Sorry if it is a complex approach for a simple thing but as I cannot share directly the pbix, though it can be the best way to test this.
In this random DM, the times it take for similar visuals are (but take into consideration that, has the department, function and role are random, they have similar amounts of rows):
The highlighted graphs are filteres for each department.
The two graphs that are still loading are like that for a long time. They basically have the whole data available but from a "real life" perspective, it would never be used.
The page is filtered frol 1 Jan 2020 until 24 Dec 2023.
The Value type is only filtered for Base 1.
The Value type considered in filtered directly into the measure.
Thanks for your help,
Pedro
hi @Anonymous
For your case, you need to create a measure as below:
Total Amount =
VAR tmpCalendar = ADDCOLUMNS('Date',"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Table',"MonthYearBegin",VALUE(YEAR([Start Date]) & FORMAT(MONTH([Start Date]),"0#")),
"MonthYearEnd",VALUE(YEAR([End Date]) & FORMAT(MONTH([End Date]),"0#")))
VAR tmpTable =
ADDCOLUMNS(
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpBilling,
SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear],"Totalworkingdays",COUNTROWS(FILTER('Date','Date'[Weekend]=FALSE()&&'Date'[Year]=[Year]&&'Date'[Month]=[Month])))
),
[MonthYear] >= [MonthYearBegin] &&
[MonthYear] <= [MonthYearEnd]
),
"Activity",[Activity],
"Year",[Year],
"Month",[Month],
"FTEs",[FTEs],
"Totalworkingdays",[Totalworkingdays],
"Start",IF(DATE([Year],[Month],1)<[Start Date],[Start Date],DATE([Year],[Month],1)),
"End",IF(DATE([Year],[Month]+1,1)-1>[End Date],[End Date],DATE([Year],[Month]+1,1)-1)
),
"Rate",DIVIDE(COUNTROWS(FILTER('Date','Date'[Weekend]=FALSE()&&'Date'[Year]=[Year]&&'Date'[Month]=[Month]&&'Date'[Date]>=[Start]&&'Date'[Date]<=[End])),[Totalworkingdays]))
return
SUMX(tmpTable,[FTEs]*[Rate])
Result:
and here is a similar post for you refer to:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
Regards,
Lin
Hello @v-lili6-msft ,
Thanks a lot for your input, quite interesting approach.
Although it worked, I faced some performance issues with the measure. I came up with a different one - that does also have performance issues - but the totals seems ok here. As it is a different aim (only improving the performance), I have posted it into a different link.
Anyway, here is the measure and the expected results (also present in the other link):
Total FTEs =
VAR _FirstDate =
FIRSTDATE ( 'DateC'[Date] )
VAR _LastDate =
LASTDATE ( 'DateC'[Date] )
RETURN
SUMX(FACTS, CALCULATE (
SUM ( FACTS[Result gross (FTE)] )
* DIVIDE (
CALCULATE (
SUM ( 'DateC'[WorkingDay] ),
DATESBETWEEN ( 'DateC'[Date], MAx(_FirstDate,SUM(FACTS[Start date])), MIN(_LastDate,SUM(FACTS[Finish date]) ))
),
Calculate(
SUM ( 'DateC'[WorkingDay] ),
DATESBETWEEN ( 'DateC'[Date], _FirstDate, _LastDate )
),
0
),
FACTS[Start date] <= _LastDate,
FACTS[Finish date] >= _FirstDate
))
Thanks a lot for your help,
Pedro
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
105 | |
98 | |
38 | |
30 |