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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Creating a Measure for Missing Scheduled Time

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):
Help.jpg

 

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

 

screen2.jpg

 

Here's the actual table 

 

screen3.jpg

 

THAAAAANK YOU!!!

Rob

 

5 REPLIES 5
v-luwang-msft
Community Support
Community Support

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
Community Support
Community Support

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
)

 

vluwangmsft_0-1638936003581.png

 

Best Regards

Lucien

Anonymous
Not applicable

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:

 

didntwork.jpg

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
Super User
Super User

@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])

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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