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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AltusTellus
Helper III
Helper III

Calculate value between two days like roasters employees

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:

 

TotalHours =
CALCULATE(SUM(tb_Schedules[AverageHours]);
    FILTER(tb_Schedules;
tb_Schedules[StartDate] >= MAX(tb_Schedules[StartDate]) &&
tb_Schedules[EndDate] <= MIN(tb_Schedules[EndDate])))

 

It looks like this:

Sample table Employments.png

 

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:

Sample overview roaster per employee.png

 

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!

1 ACCEPTED 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: 

DayName = FORMAT(dimdate[Date]; "ddd")
 
Then:

 

WeekdayYN = IF(OR(dimdate[DayName] = "za" ; (dimdate[DayName] = "zo")); "0" ; "1")

 

After these two calculated colums I added a calcuated columns in my Employments table:

 

DaysDurationEmployment = CALCULATE(SUM(dimdate[WeekdayYN]);
VAR EndDateEmployment = IF(ISBLANK(tb_Schedules[EndDate]); TODAY(); tb_Schedules[EndDate])
RETURN
DATESBETWEEN (dimdate[Date]; tb_Schedules[StartDate]; EndDateEmployment))
 
Which results in: (with related table with Employee name)
 
EmployeeStartDateEndDateDaysDurationEmployment
Annemiek25-9-2019 00:0030-9-2019 00:004
Annemiek1-10-2019 00:0031-12-2019 00:0066
Annemiek1-1-2020 00:00 98
Anouk2-9-2019 00:00 185

 

With the column [DaysDurationEmployment] I can calculate anything what I need.

 

Solved! 🙂

 

View solution in original post

14 REPLIES 14
V-pazhen-msft
Community Support
Community Support

@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.

matrix11.JPG

 

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: 

DayName = FORMAT(dimdate[Date]; "ddd")
 
Then:

 

WeekdayYN = IF(OR(dimdate[DayName] = "za" ; (dimdate[DayName] = "zo")); "0" ; "1")

 

After these two calculated colums I added a calcuated columns in my Employments table:

 

DaysDurationEmployment = CALCULATE(SUM(dimdate[WeekdayYN]);
VAR EndDateEmployment = IF(ISBLANK(tb_Schedules[EndDate]); TODAY(); tb_Schedules[EndDate])
RETURN
DATESBETWEEN (dimdate[Date]; tb_Schedules[StartDate]; EndDateEmployment))
 
Which results in: (with related table with Employee name)
 
EmployeeStartDateEndDateDaysDurationEmployment
Annemiek25-9-2019 00:0030-9-2019 00:004
Annemiek1-10-2019 00:0031-12-2019 00:0066
Annemiek1-1-2020 00:00 98
Anouk2-9-2019 00:00 185

 

With the column [DaysDurationEmployment] I can calculate anything what I need.

 

Solved! 🙂

 

V-pazhen-msft
Community Support
Community Support

@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:

 

AverageHoursEmployment IDEndDateStartDate
40Guid fieldnull1-1-2016 00:00
36Guid fieldnull1-1-2019 00:00
36Guid field31-12-2018 00:001-7-2018 00:00
24Guid fieldnull1-5-2020 00:00

 

and the other table is 'Employments' (which I use for teh [EmployeeFullName]:

 

EmployeeFullNameEmployment ID
AnnemiekGuid field
AnoukGuid field
AntoonGuid field
CarlaGuid field


Is this what you need / like to have to get to a solution? Many thnx.

V-pazhen-msft
Community Support
Community Support

@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:

 

TotalHours =
var latestdate = CALCULATE(MAX('tb_Schedules'[EndDate]);
FILTER('tb_Schedules';'tb_Schedules'[AverageHours]<>BLANK()&& 'tb_Schedules'[EndDate]<=EARLIER('tb_Schedules'[EndDate])))
Return IF([AverageHours]=BLANK();CALCULATE(MAX([AverageHours]);FILTER('tb_Schedules';[EndDate]=latestdate));[AverageHours])
 
This makes no sense unfortunately, I still have the same matrix with blank fields. What have I done wrong?

@AltusTellus 

 

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.

 

2nd column =
var latestdate = CALCULATE(MAX('tb_Schedules'[EndDate]);
FILTER('tb_Schedules';'tb_Schedules'[AverageHours]<>BLANK()&& 'tb_Schedules'[EndDate]<=EARLIER('tb_Schedules'[EndDate])))
 
Return IF([AverageHours]=BLANK();CALCULATE(MAX([AverageHours]);FILTER('tb_Schedules';[EndDate]=latestdate));[AverageHours])

 

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:

 

Employee and average hours per startdate.png

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!

@AltusTellus 

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:

 

Employee and average hours per startdate (table)_v2.png

Your explanation is clear, thnx for that. So, I have two colums extra: [TotalHours] and [TotalHours Fill Blanks]. The result is:

 

Employee and average hours per startdate_v2.png

 

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?

AltusTellus
Helper III
Helper III

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.

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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