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

[jessi82315]

Updates!!

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

Roster rable

ID	Name
0123456	John
0234567	Mary
0345678	Greg
0456789	Kite
0567890	Peter

Labor hour table

Name	Employee Num	Clock on	Clock off	Clock on Date only	Labor min	Labor Hour
Greg	05678	11/22/21 6:00 AM	11/22/21 7:00 AM	11/22/2021	60.00	1.00
Greg	05678	11/22/21 7:05 AM	11/22/21 8:10 AM	11/22/2021	65.00	1.08
Kite	06789	11/22/21 7:30 AM	11/22/21 9:11 AM	11/22/2021	101.00	1.68
Greg	05678	11/22/21 8:25 AM	11/22/21 2:10 PM	11/22/2021	345.00	5.75
Mary	02345	11/22/21 8:25 AM	11/22/21 10:49 AM	11/22/2021	144.00	2.40
John	03456	11/22/21 9:06 AM	11/22/21 10:06 AM	11/22/2021	60.00	1.00
Kite	06789	11/22/21 10:08 AM	11/22/21 12:45 PM	11/22/2021	157.00	2.62
John	03456	11/22/21 10:15 AM	11/22/21 12:45 PM	11/22/2021	150.00	2.50
Mary	02345	11/22/21 11:20 AM	11/22/21 5:02 PM	11/22/2021	342.00	5.70
John	03456	11/22/21 12:56 PM	11/22/21 5:00 PM	11/22/2021	244.00	4.07
Kite	06789	11/22/21 1:17 PM	11/22/21 4:45 PM	11/22/2021	208.00	3.47
Mary	02345	11/22/21 5:20 PM	11/22/21 7:07 PM	11/22/2021	107.00	1.78
John	03456	11/23/21 6:15 AM	11/23/21 8:00 AM	11/23/2021	105.00	1.75
Greg	05678	11/23/21 7:00 AM	11/23/21 11:06 AM	11/23/2021	246.00	4.10
Mary	02345	11/23/21 7:45 AM	11/23/21 10:17 AM	11/23/2021	152.00	2.53
John	03456	11/23/21 8:06 AM	11/23/21 10:26 AM	11/23/2021	140.00	2.33
John	03456	11/23/21 10:55 AM	11/23/21 1:57 PM	11/23/2021	182.00	3.03
Greg	05678	11/23/21 2:06 PM	11/23/21 3:06 PM	11/23/2021	60.00	1.00
John	03456	11/24/21 6:00 AM	11/24/21 8:11 AM	11/24/2021	131.00	2.18
Kite	06789	11/24/21 6:06 AM	11/24/21 8:06 AM	11/24/2021	120.00	2.00
Mary	02345	11/24/21 6:10 AM	11/24/21 8:06 AM	11/24/2021	116.00	1.93
Greg	05678	11/24/21 6:16 AM	11/24/21 8:01 AM	11/24/2021	105.00	1.75
Greg	05678	11/24/21 8:06 AM	11/24/21 11:06 AM	11/24/2021	180.00	3.00
Kite	06789	11/24/21 8:20 AM	11/24/21 10:55 AM	11/24/2021	155.00	2.58
John	03456	11/24/21 8:25 AM	11/24/21 11:06 AM	11/24/2021	161.00	2.68
Kite	06789	11/24/21 11:00 AM	11/24/21 11:55 AM	11/24/2021	55.00	0.92
Greg	05678	11/24/21 11:26 AM	11/24/21 3:36 PM	11/24/2021	250.00	4.17
Kite	06789	11/24/21 1:00 PM	11/24/21 3:00 PM	11/24/2021	120.00	2.00
John	03456	11/24/21 1:27 PM	11/24/21 4:16 PM	11/24/2021	169.00	2.82
Peter	07890	11/24/21 6:54 AM	11/24/21 8:32 AM	11/24/2021	98.00	1.63
Peter	07890	11/24/21 9:14 AM	11/24/21 11:32 AM	11/24/2021	138.00	2.30
Peter	07890	11/24/21 1:54 PM	11/24/21 5:12 PM	11/24/2021	198.00	3.30

Shift change record table

Employee Num	Name	Shift Change hour	Chift change date
02345	Mary	10	11/22/2021
05678	Greg	9	11/24/2021

vacation record table

Employee Num	Name	time off hours	vacation date
06789	Kite	8	11/23/2021
02345	Mary	4	11/23/2021
05678	Greg	2	11/23/2021
02345	Mary	5	11/24/2021

What I want to achieve:

date filter: past __3__ Days

	22-Nov	23-Nov	24-Nov	Total
Employee Num	Clock in hour	Availabel hour	Clock in hour	Availabel hour	Clock in hour	Availabel hour	Clock in hour	Availabel hour
02345	9.88	10	2.53	4	1.93	3	14.35	17
03456	7.57	8	7.12	8	7.68	8	22.37	24
05678	7.83	8	5.10	6	8.92	9	21.85	23
06789	7.77	8	0.00	0	7.50	8	15.27	16
07890	0.00	8	0.00	8	7.23	8	7.23	24
Total	33.05	42.00	14.75	26.00	33.27	36.00	81.07	104.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:

Labor Table:

Shift schedule change:

Time off :

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.

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!!!

[v-angzheng-msft · Accepted solution]

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.

Result:

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.

[jessi82315]

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

[v-angzheng-msft]

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.

[jessi82315]

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 · Accepted solution]

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.

[jessi82315]

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

Thanks again!!!! 😀

[v-angzheng-msft]

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.

[jessi82315]

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

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

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

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.

[jessi82315]

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

Thank you so much,

Jessie

[v-angzheng-msft · Accepted solution]

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.

Result:

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.

[jessi82315]

@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

[jessi82315]

@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

[jessi82315]

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