Forum Discussion
Calculate Rolling Sickness Rate From Cumulative DataSet
- 6 months ago
Step 1) Create a proper Star-Schema model based on the data you have
Since you have only 2 tables here you need to create dims and fact via PowerQuery. First create Employee and Date dimensions and create a proper FactSickness table to allocate your sickness data on a daily grain.
Step 2) Create required measures
Available FTE (Month) = VAR mStart = MAX ( DimDate[MonthStart] ) VAR mEnd = EOMONTH ( mStart, 0 ) RETURN CALCULATE ( SUM ( FactEmployee[FTE] ), FILTER ( FactEmployee, FactEmployee[Start Date] <= mEnd && ( ISBLANK ( FactEmployee[Termination Date] ) || FactEmployee[Termination Date] >= mStart ) ) ) Available FTE (Rolling 12M) = CALCULATE ( [Available FTE (Month)], DATESINPERIOD ( DimDate[MonthStart], MAX ( DimDate[MonthStart] ), -12, MONTH ) ) Sickness Lost (Rolling 12M) = CALCULATE ( SUM ( FactSickness[DailyFTE_Lost] ), DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH ) ) Sickness Rate % (Rolling 12M) = DIVIDE ( [Sickness Lost (Rolling 12M)], [Available FTE (Rolling 12M)] )Step 3) Create your visual using correct field values
Please fint the attached .pbix file for end-to-end solution.
Step 1) Create a proper Star-Schema model based on the data you have
Since you have only 2 tables here you need to create dims and fact via PowerQuery. First create Employee and Date dimensions and create a proper FactSickness table to allocate your sickness data on a daily grain.
Step 2) Create required measures
Available FTE (Month) =
VAR mStart = MAX ( DimDate[MonthStart] )
VAR mEnd = EOMONTH ( mStart, 0 )
RETURN
CALCULATE (
SUM ( FactEmployee[FTE] ),
FILTER (
FactEmployee,
FactEmployee[Start Date] <= mEnd
&& ( ISBLANK ( FactEmployee[Termination Date] )
|| FactEmployee[Termination Date] >= mStart )
)
)
Available FTE (Rolling 12M) =
CALCULATE (
[Available FTE (Month)],
DATESINPERIOD ( DimDate[MonthStart], MAX ( DimDate[MonthStart] ), -12, MONTH )
)
Sickness Lost (Rolling 12M) =
CALCULATE (
SUM ( FactSickness[DailyFTE_Lost] ),
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)
Sickness Rate % (Rolling 12M) =
DIVIDE ( [Sickness Lost (Rolling 12M)], [Available FTE (Rolling 12M)] )
Step 3) Create your visual using correct field values
Please fint the attached .pbix file for end-to-end solution.
Hi cengizhanarslan - I amended the measures slightly as I had to take into account the dates when the FTE changed for staff members but this has worked, thank you so much!