cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jessi82315
Helper I
Helper I

measure to look up value and added to total and be dependent by date for fields with no data

Updates!!

 

Here's some sample data to better address my goal.

 

Roster rable

IDName
0123456John
0234567Mary
0345678Greg
0456789Kite
0567890Peter

 

Labor hour table

NameEmployee NumClock onClock offClock on Date onlyLabor minLabor Hour
Greg0567811/22/21 6:00 AM11/22/21 7:00 AM11/22/202160.001.00
Greg0567811/22/21 7:05 AM11/22/21 8:10 AM11/22/202165.001.08
Kite0678911/22/21 7:30 AM11/22/21 9:11 AM11/22/2021101.001.68
Greg0567811/22/21 8:25 AM11/22/21 2:10 PM11/22/2021345.005.75
Mary0234511/22/21 8:25 AM11/22/21 10:49 AM11/22/2021144.002.40
John0345611/22/21 9:06 AM11/22/21 10:06 AM11/22/202160.001.00
Kite0678911/22/21 10:08 AM11/22/21 12:45 PM11/22/2021157.002.62
John0345611/22/21 10:15 AM11/22/21 12:45 PM11/22/2021150.002.50
Mary0234511/22/21 11:20 AM11/22/21 5:02 PM11/22/2021342.005.70
John0345611/22/21 12:56 PM11/22/21 5:00 PM11/22/2021244.004.07
Kite0678911/22/21 1:17 PM11/22/21 4:45 PM11/22/2021208.003.47
Mary0234511/22/21 5:20 PM11/22/21 7:07 PM11/22/2021107.001.78
John0345611/23/21 6:15 AM11/23/21 8:00 AM11/23/2021105.001.75
Greg0567811/23/21 7:00 AM11/23/21 11:06 AM11/23/2021246.004.10
Mary0234511/23/21 7:45 AM11/23/21 10:17 AM11/23/2021152.002.53
John0345611/23/21 8:06 AM11/23/21 10:26 AM11/23/2021140.002.33
John0345611/23/21 10:55 AM11/23/21 1:57 PM11/23/2021182.003.03
Greg0567811/23/21 2:06 PM11/23/21 3:06 PM11/23/202160.001.00
John0345611/24/21 6:00 AM11/24/21 8:11 AM11/24/2021131.002.18
Kite0678911/24/21 6:06 AM11/24/21 8:06 AM11/24/2021120.002.00
Mary0234511/24/21 6:10 AM11/24/21 8:06 AM11/24/2021116.001.93
Greg0567811/24/21 6:16 AM11/24/21 8:01 AM11/24/2021105.001.75
Greg0567811/24/21 8:06 AM11/24/21 11:06 AM11/24/2021180.003.00
Kite0678911/24/21 8:20 AM11/24/21 10:55 AM11/24/2021155.002.58
John0345611/24/21 8:25 AM11/24/21 11:06 AM11/24/2021161.002.68
Kite0678911/24/21 11:00 AM11/24/21 11:55 AM11/24/202155.000.92
Greg0567811/24/21 11:26 AM11/24/21 3:36 PM11/24/2021250.004.17
Kite0678911/24/21 1:00 PM11/24/21 3:00 PM11/24/2021120.002.00
John0345611/24/21 1:27 PM11/24/21 4:16 PM11/24/2021169.002.82
Peter0789011/24/21 6:54 AM11/24/21 8:32 AM11/24/202198.001.63
Peter0789011/24/21 9:14 AM11/24/21 11:32 AM11/24/2021138.002.30
Peter0789011/24/21 1:54 PM11/24/21 5:12 PM11/24/2021198.003.30

 

Shift change record table

Employee NumNameShift Change hourChift change date
02345Mary1011/22/2021
05678Greg911/24/2021

 

vacation record table

Employee NumNametime off hoursvacation date
06789Kite811/23/2021
02345Mary411/23/2021
05678Greg211/23/2021
02345Mary511/24/2021

 

What I want to achieve:

date filter: past __3__ Days

 22-Nov23-Nov24-NovTotal
Employee NumClock in hourAvailabel hourClock in hourAvailabel hourClock in hourAvailabel hourClock in hourAvailabel hour
023459.88102.5341.93314.3517
034567.5787.1287.68822.3724
056787.8385.1068.92921.8523
067897.7780.0007.50815.2716
078900.0080.0087.2387.2324
Total33.0542.0014.7526.0033.2736.0081.07104.00

 

I want the report to be able to dynamically capture actual clock in hour vs available hours and be able to show the total for both row and column.

 

Hope this makes it more clear to understand!!!

 

Thank you!

 

****************************************Original Post*****************************************************

Hello all,

I'm working on a labor hour report.

There are 4 tables.

Column with red cross is the primary key.

Roster:

jessi82315_1-1637789880569.png

Labor Table:

jessi82315_2-1637789897376.png

Shift schedule change:

jessi82315_3-1637789949957.png

Time off :

jessi82315_4-1637789990249.png

 

I'm trying to build a talbe where I can:

1. filter by date to look at the labor utilization rate throughout the week/month/year...etc.

2. show clock-in hour vs available hours for each employee.

3. total for the day of clock in hour and available hour.

 

Factors:

1. shift schedule is not always 8 hours.

    so I create a table to submit shift change record.

2. need to consider time-off.

    there's a table for time-off records too.

 

Problem: 

1. I added a column in labor hour table to calculate available hours. So each clock in records has an available hours. But if the employee for some reason didn't clock in for the day that he is available, since there is no clock in records. The person doesn't have a availalbe hours calculated, thus it shows blank for "clock-in hour" and "available hours".(see belowpicture) This makes the Total available hour skewed, from below picture, if the 2 person are not off, the available hours should be 616 not 600.

jessi82315_6-1637790975662.png

I was able to make a measure to show 0 for "clock-in hour" but for "available hours" it gets tricky.

If I make another measure to check 1. if that person is off and 2. if his shift got changed, First of all, I don't even know if I could make this measure work. Second, the problem will be: even if I get this to work, the numbers won't be added to the total.

 

 

I'm totally lost now...maybe it is wrong to add the available hours to the clock in table? 

I was trying to avoid making a big table to store every employees' available hours for every single day in a year...

 

Any suggestions would be greatly appreciated....!!!

 

Thanks!!!

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Hi, @jessi82315 

Yeah, I know. Because I did not consider the filter when calculating the total.
Try to add filters like below, you can get the correct result.

vangzhengmsft_0-1638410466697.png

Result:

vangzhengmsft_0-1638410774641.png

 

It is indeed close to three hours, but this will not be beyond your knowledge. There must be an easier way, but at the time this solution was the only one I thought of.

I have learned a lot about this case, thank you for bringing it up.😉

 

Please refer to the attachment below for details. Hope this helps.

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-angzheng-msft
Community Support
Community Support

Hi, @jessi82315 

This logic seems to be correct. If it does not react, check whether filter is added to all VARs(needed for the calculation), and check whether there is a filtering relationship between the slicer and other fields

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
jessi82315
Helper I
Helper I

 

Hi  @v-angzheng-msft 

Hope you are doing well! I just found another question I never got it cleared. (and it may be a dumb question...Please bear with me )

Multiple variables in the measure like below this one: the filter for the date

 

var _sum=CALCULATE(SUM('vacation record table'[time off hours]),FILTER(ALL('vacation record table'),'vacation record table'[Employee Num]=MAX('Labor hour table'[Employee Num])&&'vacation record table'[vacation date]=MAX('ColumnOfMatrix'[Clock on Date only])))

 

When filtering through the data, how come using MAX can get us the correct result?

I understand measure is a aggregated function based and cannot be perform without using these MAX, SUM...etc. functions but is it not always looking at the Maximum date to match???? 

 

Thanks

Jessie

Hi, @jessi82315 

This is definitely not a dumb question, on the contrary it's a good one and a lot of people are struggling here.
I did an explanation in a previous thread, you can read it to help understand.
Question regarding syntac/ context transition cumulative sum

 

 

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-angzheng-msft ,

 

sorry for the late reply! I had been reading your post and learning all these stuff. probably got too excited and forgot to reply 🤣 But yes!! that is very helpful!!! Really appreciate your BIG HELP 💪

 

Thank you!

Jessie

v-angzheng-msft
Community Support
Community Support

Hi, @jessi82315 

This logic seems to be correct. If it does not react, check whether filter is added to all VARs(needed for the calculation), and check whether there is a filtering relationship between the slicer and other fields

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-angzheng-msft Thank you!! I added some relationship between the tables and it does work now. 

Thanks again!!!! 😀

v-angzheng-msft
Community Support
Community Support

Hi, @jessi82315 

 

Could you please consdier sharing a simple sample file without any sesentive information and posting expected result so it is clear on what needs to be implemented?  It makes it easier to give you a solution.

 

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hmmm now I can't even see any replies...

I hope you can see my reply! @v-angzheng-msft !!

Hi, @jessi82315 

Don't worry, I have already requested to remove the post from spam.
Okay, I have seen your reply. Please note: If the reply is edited too many times, the post will be marked as spam.


It took me close to three hours to process your case, and finally I got the solution.


Here is the solution:
First create three auxiliary tables:

ColumnOfMatrix = VALUES('Labor hour table'[Clock on Date only])
Shift All = 
var _t1=SUMMARIZE('Labor hour table',[Employee Num],[Name])
var _t2=VALUES('Labor hour table'[Clock on Date only])
var _t=GENERATE(_t1,_t2)
var _except=EXCEPT(_t,SUMMARIZE('Shift change record table',[Employee Num],[Name],[Chift change date]))

var _add=ADDCOLUMNS(_except,"Shift Change hour",8)
var _name=SELECTCOLUMNS(_add,"Empoyee Num",[Employee Num],"Name",[Name],"Shift Change hour",[Shift Change hour],"Chift change date",[Clock on Date only])
var _union=UNION('Shift change record table',_name)
return _union
Vacation All = 
var _t1=SUMMARIZE('Labor hour table',[Employee Num],[Name])
var _t2=VALUES('Labor hour table'[Clock on Date only])
var _t=GENERATE(_t1,_t2)
var _except=EXCEPT(_t,SUMMARIZE('vacation record table',[Employee Num],[Name],[vacation date]))

var _add=ADDCOLUMNS(_except,"time off hours",0)
var _name=SELECTCOLUMNS(_add,"Empoyee Num",[Employee Num],"Name",[Name],"time off hours",[time off hours],"vacation dadte",[Clock on Date only])
var _union=UNION('vacation record table',_name)
return _union

To create four measures:

_Clock in hour = 
var _clock=CALCULATE(SUM('Labor hour table'[Labor Hour]),FILTER(ALL('Labor hour table'),'Labor hour table'[Clock on Date only]=MAX('ColumnOfMatrix'[Clock on Date only])&&'Labor hour table'[Employee Num]=MAX('Labor hour table'[Employee Num])))
var _if=IF(_clock=BLANK(),0,_clock)

var _column=CALCULATE(SUM('Labor hour table'[Labor Hour]),FILTER(ALL('Labor hour table'),'Labor hour table'[Employee Num]=MAX('Labor hour table'[Employee Num])))
var _row= CALCULATE(SUM('Labor hour table'[Labor Hour]),FILTER(ALL('Labor hour table'),'Labor hour table'[Clock on Date only]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _total=CALCULATE(SUM('Labor hour table'[Labor Hour]))

var _switch=
    SWITCH(
        TRUE(),
        not(HASONEVALUE('Labor hour table'[Employee Num]))&&not(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
        // 4,
        _total,
        not(HASONEVALUE('Labor hour table'[Employee Num])),
        // 3,
        _row,
        not(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
        // 2,
        _column,
        // 1
        _if
        )
return _switch
_ShiftChange = 
var _shiftChange=
    CALCULATE(MAX('Shift change record table'[Shift Change hour]),
        FILTER(ALL('Shift change record table'),'Shift change record table'[Employee Num]=MAX('Labor hour table'[Employee Num])&&'Shift change record table'[Chift change date]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _if=COALESCE(_shiftChange,8)

var _column=CALCULATE(SUM('Shift All'[Shift Change hour]),FILTER(ALL('Shift All'),'Shift All'[Employee Num]=MAX('Labor hour table'[Employee Num])))
var _row= CALCULATE(SUM('Shift All'[Shift Change hour]),FILTER(ALL('Shift All'),'Shift All'[Chift change date]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _total=CALCULATE(SUM('Shift All'[Shift Change hour]))

var _switch=
    SWITCH(
        TRUE(),
        not(HASONEVALUE('Labor hour table'[Employee Num]))&&not(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
        // 4,
        _total,
        not(HASONEVALUE('Labor hour table'[Employee Num])),
        // 3,
        _row,
        not(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
        // 2,
        _column,
        // 1
        _if
        )
return _switch
_timeOff = 
var _sum=CALCULATE(SUM('vacation record table'[time off hours]),FILTER(ALL('vacation record table'),'vacation record table'[Employee Num]=MAX('Labor hour table'[Employee Num])&&'vacation record table'[vacation date]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _if=COALESCE(_sum,0)

var _column=CALCULATE(SUM('Vacation All'[time off hours]),FILTER(ALL('Vacation All'),'Vacation All'[Employee Num]=MAX('Labor hour table'[Employee Num])))
var _row= CALCULATE(SUM('Vacation All'[time off hours]),FILTER(ALL('Vacation All'),'Vacation All'[vacation date]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _total=CALCULATE(SUM('Vacation All'[time off hours]))

var _switch=
    SWITCH(
        TRUE(),
        not(HASONEVALUE('Labor hour table'[Employee Num]))&&not(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
        // 4,
        _total,
        not(HASONEVALUE('Labor hour table'[Employee Num])),
        // 3,
        _row,
        not(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
        // 2,
        _column,
        // 1
        _if
        )
return _switch
_Available = [_ShiftChange]-[_timeOff]



Result:

vangzhengmsft_0-1638267324819.png

Please refer to the attachment below for details. Hope this helps.

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@v-angzheng-msft 

 

you're amazing....!!!😲 I mean 3 hours to a solution like this is beyond my knowledge!!! (still have a lot to learn...)

 

I was studying your solution and got a question!

Which date field will be the right one to filter through these numbers?

I tried the "Clock on Date only" in "ColumnOfMatrix" table, it was working good but the Total is the same for filtering for 2 days and 3 days. See below:

It looks like Total for 2 days is not adding up correctly. Do you know why?

jessi82315_0-1638369269403.png

jessi82315_1-1638369282966.png

 

Thank you so much,

Jessie

Hi, @jessi82315 

Yeah, I know. Because I did not consider the filter when calculating the total.
Try to add filters like below, you can get the correct result.

vangzhengmsft_0-1638410466697.png

Result:

vangzhengmsft_0-1638410774641.png

 

It is indeed close to three hours, but this will not be beyond your knowledge. There must be an easier way, but at the time this solution was the only one I thought of.

I have learned a lot about this case, thank you for bringing it up.😉

 

Please refer to the attachment below for details. Hope this helps.

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-angzheng-msft 

 

Could I ask 1 more question?

I have other columns in the "Labor Hour" table as "Job Number".

I was trying to add filter to the measures so it would respond to it when I have a "Job Number" filter active but it doesn't seem to react? I did it same way as how you add the filter for the date: var _row = CALCULATE(SUM('Labor hour table'[Labor Hour]),FILTER(ALL('Labor hour table'),'Labor hour table'[Clock on Date only]=MAX('ColumnOfMatrix'[Clock on Date only]&&'Labor hour table'[Clock on Date only] in ALLSELECTED(ColumnOfMatrix[Clock on Date only]&&'Labor hour table'[Job Number] in ALLSELECTED('Labor hour table'[Job Number])))

I'm still learning all the DAX funcitons you used in the solution and wondering maybe there's something that I'm missing?

 

Any helps is appreciated!!! 

Thanks

Jessie

 

@v-angzheng-msft 

 

You are AMAZING!!!!! I'm learning a lot from you too!

There are still a lot about Power BI I haven't learned and the way you do it definitely brings a new perspective to solve problems!

 

Thanks again for your help!!

Much appreciated!

 

Thanks

Jessie

@v-angzheng-msft 

 

Hello!! I'm sorry I'm not sure why but when I hit submit after editing the post and it marked your reply as spam....!!!

I didn't do it on purpose and don't know how to undo it either...! 😂

 

if you would please look at my updated post again!! any help is greatly appreciated!!

 

sorry again about accidentally delete your reply...!

 

thanks!!!

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!