Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
For a couple of days now, I'm thinking about to calculate the number of hours per employee from the past until today.
I've a table with Employments. In this table I have a start date, an end date (can also be blank) and a EmployeeHID which refers to another table with the FullName of the Employee. To get the roaster hours (based on the average hours per week) I have the next DAX:
It looks like this:
PLease take this info: the first three rows are for the same employee. Now I want to get this information in a matrix so I canb make an overview of the avarage hours per week / month et cetera. But that looks like this:
As you see, I still have to make an additional formula for the average hours per month, but the most wanted answer is on my question: How can I extend the latest value of the roasters in de "blank" periodes. The employee is still employed, how can I fill the last value into the blank fields in the periodes? And of course, there are more employees and I would like to calculate the whole capacity in hours for the whole organisation.
Anyone know how? Thank you very much!
Solved! Go to Solution.
Hi Paul,
Thnx again foor your reply. It gives me another thought about calculating the days (or hours et cetera) between the start date of an employment including the end date. In my dimdate table I have calculated column which gives me the working days per week (from Monday until Friday:
First:
After these two calculated colums I added a calcuated columns in my Employments table:
Employee | StartDate | EndDate | DaysDurationEmployment |
Annemiek | 25-9-2019 00:00 | 30-9-2019 00:00 | 4 |
Annemiek | 1-10-2019 00:00 | 31-12-2019 00:00 | 66 |
Annemiek | 1-1-2020 00:00 | 98 | |
Anouk | 2-9-2019 00:00 | 185 |
With the column [DaysDurationEmployment] I can calculate anything what I need.
Solved! 🙂
@AltusTellus
Thank you for the sample, this is clear now. But unfortunately your expected solution cannot be reach with your data table. Because in your table there is no value recorded for each employee under other start/end dates. Sounds a bit confusing, in other word if you want to fill the blanks, you at least should have a row to records those blanks under the others dates even there are 0 or blank values.
Best regards
Paul Zheng
Hi Paul,
Thnx again foor your reply. It gives me another thought about calculating the days (or hours et cetera) between the start date of an employment including the end date. In my dimdate table I have calculated column which gives me the working days per week (from Monday until Friday:
First:
After these two calculated colums I added a calcuated columns in my Employments table:
Employee | StartDate | EndDate | DaysDurationEmployment |
Annemiek | 25-9-2019 00:00 | 30-9-2019 00:00 | 4 |
Annemiek | 1-10-2019 00:00 | 31-12-2019 00:00 | 66 |
Annemiek | 1-1-2020 00:00 | 98 | |
Anouk | 2-9-2019 00:00 | 185 |
With the column [DaysDurationEmployment] I can calculate anything what I need.
Solved! 🙂
@AltusTellus
I am sorry, due to the policy we cannot use personal message. You could create a short sample with random value in the columns. So I could test on it. Thanks
Paul
Hi,
The table 'Schedules' looks like:
AverageHours | Employment ID | EndDate | StartDate |
40 | Guid field | null | 1-1-2016 00:00 |
36 | Guid field | null | 1-1-2019 00:00 |
36 | Guid field | 31-12-2018 00:00 | 1-7-2018 00:00 |
24 | Guid field | null | 1-5-2020 00:00 |
and the other table is 'Employments' (which I use for teh [EmployeeFullName]:
EmployeeFullName | Employment ID |
Annemiek | Guid field |
Anouk | Guid field |
Antoon | Guid field |
Carla | Guid field |
Is this what you need / like to have to get to a solution? Many thnx.
@AltusTellus
Is the dax you have now a measure or a column?I would recommend you to create the TotalHours with a calculated column, then create the following column as the result to fill the blanks:
Result =
var latestdate = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Totalhours]<>BLANK()&& [Date]<=EARLIER('Table'[Date])))
Return IF([Totalhours]=BLANK(),CALCULATE(MAX([Totalhours]),FILTER('Table',[Date]=latestdate)),'Table'[Totalhours])
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Paul,
Based on your input (thnx!) I made the follow calculated column:
You need 2 columns in total, the 1st column is the TotalHours that has blank fields in it. You already have it in a measure but you need to change it to a column.
The 2nd column is the result column you want, the logic is IF the 1st column [TotalHours] is blank, you returns the sales of the latestdate. You need to change the [AverageHours] to [TotalHours] (1st column has blanks) you created.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Paul,
I think we are almost there, but I don't understand your DAX completely. Extra info: the table 'tb_Schedules' has several columns like [AverageHours], [EmployeeHID], [StartDate] and [EndDate]. There are no blanks in the columns.
In the table 'dimdate' (which contains all datums from 2013 until 2099) the field [Date] is connected with the [StartDate] from the table 'tb_Schedules'. In a matrix I put the [Employee] as rows and the [Date] as columns. With your DAX the result is:
You see that this employee has a schedule that started at the 25th of september 2019 and has a new schedule per the 1st of October. After that I have blanks until a new schedule for this employee per the 1st of January 2020.
What I would like to see (and use for further calculations) is that there are no blanks but instead the last value of the current schedule until the sequenced schedule. Please note that this screenshot is with the days as columns, I will went to weeknumbers.
Can you help me further please? Many thanks!
You could share you sample if you couldn't get my following explanation. I explain the logic of the dax in two parts.
1. The current situation is you have already create the following [TotalHours] column, but you have blanks and you want fill the blanks with latest nonblank value from current schedule.
TotalHours =
CALCULATE(SUM(tb_Schedules[AverageHours]);
FILTER(tb_Schedules;
tb_Schedules[StartDate] >= MAX(tb_Schedules[StartDate]) &&
tb_Schedules[EndDate] <= MIN(tb_Schedules[EndDate])))
2. To fill the blank with latest nonblank value, you first find the latest date of current schedule that has a value with "VAR lastesdate", then return with a IF fucntion, if the [TotalHours] is blank return the value of lasted non blank value at that time, If not blank just return [TotalHours]. This is the column you should put in the matrix:
TotalHours Fill Blanks =
VAR latestdate = CALCULATE(MAX('tb_Schedules'[EndDate]);
FILTER('tb_Schedules';'tb_Schedules'[TotalHours]<>BLANK()&& 'tb_Schedules'[EndDate]<=EARLIER('tb_Schedules'[EndDate])))
Return IF([TotalHours]=BLANK();CALCULATE(MAX([TotalHours]);FILTER('tb_Schedules';[EndDate]=latestdate));[TotalHours])
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
And this is how it's look like in a table:
Your explanation is clear, thnx for that. So, I have two colums extra: [TotalHours] and [TotalHours Fill Blanks]. The result is:
Is that because we have forgotten to filter also on the column [Employee]? Or something else?
If needed: the information is confidential, can I share my file by sending DM to you?
Additional info: this tabe with Schedules is connected to my dimdate table. I think it has something to do with ADDCOLUMNS but I don't know how to do that.
You'll need something along the lines of Open Tickets. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147
Sample data as text would be great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Many thanks for your reply! In the article you refer to I'm missing some extra info about the tables in the DAX-example. I don't get it work in my report unfortunately.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |