Allocate Man hour equally

[hongyuliu]

I have a table like following, I need to allocate the man hour equally to each day, and then calculate the man hour of each staff per month

Pls help, thank you very much in advance

[v-shex-msft]

HI @hongyuliu,

I'm not so sure what you mean, can you please share some expected results to help us clarify your requirement?
In addition, if you mean expand these date range and summary daily man hour and calculate the average man-hour based on daily total and staff count.
You can create a calculated table to expand date range and link with staffs, then you can create a visual with date as axis and aggregate staff cont as value.

Expand = 
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN (
            'Table',
            CALENDAR ( MIN ( 'Table'[Date From] ), MAX ( 'Table'[Date To] ) )
        ),
        [Date] >= [Date From]
            && [Date] <= [Date To]
    ),
    "Staff", [Staff],
    "Date", [Date]
)

Spread revenue across period based on start and end date, slice and dase this using different dates 

Regards,

Xiaoxin Sheng

[hongyuliu]

Hi

I need to calculate how many hours one staff has worked in a month. For example, Staff A started working on Sep 30, finishing on Nov 30, totaly working 471 hours in 60 days. Then 471/60= 7.85 hour/day. In september, Staff A has worked 7.85 hours. I just dont' know how to write the formula.

my original data table is like this

Result should be like this, if I create a Matrix in Power BI

Thank you very much for your help.

[Greg_Deckler · Accepted solution]

Seems like a combination of:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111

And

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

[amitchandak]

@hongyuliu 

Refer : https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...