Creating a Measure for Missing Scheduled Time

[Anonymous]

Good day,

I am VERY new to measures and could use some guidance please. I have a table consisting of scheduled events by employees in the future. (I have a date table that maps week numbers to each date, however in the sample data below I combined it all into one table). I'm trying to somehow add a measure to my schedule table that I can drag into the second picture below to add my 'missing time' for each week. By the missing time, I mean any employee's time that is < 8 hours for that day. So, for example, if the employee had two scheduled events for a day, each 3 hours, the measure would show 2 hours for that day.

Here's a simplified example of what I'm trying to evaluate (the yellow highlight):

Here's a screen shot of my report (only missing the data for the missing time in each week)

Here's the actual table 

THAAAAANK YOU!!!

Rob

[v-luwang-msft]

Hi @Anonymous ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[v-luwang-msft]

Hi  @Anonymous ,

Test the below measure:

test1 =
IF (
    CALCULATE (
        SUM ( 'Table'[Hours Scheduled] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Employee] = MAX ( 'Table'[Employee] )
        )
    ) <= 8,
    8
        - CALCULATE (
            SUM ( 'Table'[Hours Scheduled] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Date] = MAX ( 'Table'[Date] )
                    && 'Table'[Employee] = MAX ( 'Table'[Employee] )
            )
        ),
    0
)

Best Regards

Lucien

[Anonymous]

Amit,

I tried this:

RemainingHours = Sumx(SUMMARIZE(ScheduleData, DateTable[Week], ScheduleData[Member_ID], "_1", SUM(ScheduleData[Hours_Sched])), 8-[_1])

and it didn't appear to work:

I'm sure it's something in my translation of your formula. I'm also curious what the "_1" parameter is doing?

Thank you,

Rob

[amitchandak]

@Anonymous , based on what I got. You can get remaining hour and use that with scheduled hour in stacked visual

Remaining Hour =Sumx(Summmarize(Table, Table[Week], Table[Employee], "_1", Sum(Table[Scheduled Hour])), 8- [_1])

[Anonymous]

Amit, 

First, THANK YOU! Just a couple of follow-up questions please:

1. The _1 - is that literally just that, or is that shorthand for something? What function does that serve please?
2. If the fields are in the same table (ie the second screen shot above you can see "Member_ID" (which is there first intial and last name concantenated) and "Hours_Sched" is in the same table. (With multiple events being scheduled in that table obviously). The Week # is just pulled from a Date Table that has a relationship from the Date_Time_Start to the Date (of the Dates table. All that table is is just a list of dates with the week and qtr #).
3. Like I said, I'm new to DAX and so (ONLY if you have a spare moment) if you could kinda summarize the logic of what's happening you'd have NO idea how much that would help me

Amit - thank you SO much for your quick and thoughtful response!

-Rob