Timesheet Hours Calculation

[nathan115]

Hi community,

I am new to Power BI, and I have a question about calculating timesheet hours. I have a timesheet table (sample) as follow:

Project	Resource	Resource Type	Timesheet Start	Timesheet End	Timesheet Month	Hours	Timesheet Status	Timesheet Creation	Forecast Hours
A	John	Employee	9/23/2018	9/29/2018	9	8	Frozen	9/25/2018	20.00
A	John	Employee	9/23/2018	9/29/2018	9	16	Frozen	9/24/2018	20.00
B	John	Employee	9/16/2018	9/22/2018	9	10	Pending	9/17/2018	30.00
B	John	Employee	9/16/2018	9/22/2018	9	10	Frozen	9/18/2018	30.00

There are two types of status: frozen and pending. I need to create 2 calculations in the table: Submitted Hours (only include pending hours in a week) and Frozen Hours (aggregate hours for all the previous weeks within curren timesheet month, and less than timesheet creation date). The expected result will be as follow: 

Project	Resource	Resource Type	Timesheet Start	Timesheet End	Timesheet Month	Hours	Timesheet Status	Timesheet Creation	Forecast Hours	Frozen Hours	Submitted Hours
A	John	Employee	9/23/2018	9/29/2018	9	8	Frozen	9/25/2018	20.00	34	0
A	John	Employee	9/23/2018	9/29/2018	9	16	Frozen	9/24/2018	20.00	26	0
B	John	Employee	9/16/2018	9/22/2018	9	10	Pending	9/17/2018	30.00	10	10
B	John	Employee	9/16/2018	9/22/2018	9	10	Frozen	9/18/2018	30.00	10	0

Also, for the card visuals, I want to have 2 values: total of forecast hours per project per resource per timesheet month, and total of frozen hours per resource per month, so in this case, Forecast Hours (card visual) will be 50, and Frozen Hours will be 34. This part is optional, so in the meantime I am looking for Frozen hours and Submitted Hours calculation only.

I would really appreciate for your answers!

[nathan115]

@v-yulgu-msft,

I am trying to create the frozen hour as a measure to make it more dynamic, but I got an error saying "EARLIER refers to an earlier row context which doesn't exit". Do you know how I can modify it to make the frozen hour a measure instead?

[v-yulgu-msft · Accepted solution]

Hi @nathan115,

Please create two calculated columns like below.

Submitted Hours =
IF (
    TimeSheet[Timesheet Status] = "Pending",
    CALCULATE (
        SUM ( TimeSheet[Hours] ),
        FILTER (
            ALLEXCEPT (
                TimeSheet,
                TimeSheet[Timesheet Month],
                TimeSheet[Timesheet Start],
                TimeSheet[Timesheet End]
            ),
            TimeSheet[Timesheet Status] = "Pending"
        )
    ),
    0
)

Frozen Hours =
CALCULATE (
    SUM ( TimeSheet[Hours] ),
    FILTER (
        ALLEXCEPT ( TimeSheet, TimeSheet[Timesheet Month] ),
        TimeSheet[Timesheet Creation] <= EARLIER ( TimeSheet[Timesheet Creation] )
            && TimeSheet[Timesheet Status] = "Frozen"
    )
)
    + 0

For the two values displayed in card visual, please refer to these measures:

total of forecast hours =
VAR temptable =
    SUMMARIZE (
        TimeSheet,
        TimeSheet[Project],
        TimeSheet[Resource],
        TimeSheet[Timesheet Month],
        TimeSheet[Timesheet Start],
        TimeSheet[Timesheet End],
        "Total Forecast", FIRSTNONBLANK ( TimeSheet[Forecast Hours], 1 )
    )
RETURN
    SUMX ( temptable, [Total Forecast] )


total of frozen hours =
CALCULATE (
    MAX ( TimeSheet[Frozen Hours] ),
    ALLEXCEPT (
        TimeSheet,
        TimeSheet[Project],
        TimeSheet[Resource],
        TimeSheet[Timesheet Month]
    )
)

Best regards,

Yuliana Gu

[nathan115]

Thank you Yuliana! I have to modify couple things here and there, but other than that, the formula works like a charm!