cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
datavis Resolver I
Resolver I

Contract Start and End Dates calculation for remaining hours

September is the first month of our fiscal year. I subtract [Hours Worked] from [PO Hours] to get remaining hours. If no hours are reported, then subtract hours available to work. Subsequent months subtract from the previous month instead of PO Hours.

 


Sept Remaining Hours = IF(FY19RemainingHours[Sep 2018]>0,

FY19RemainingHours[ PO Hours] - FY19RemainingHours[Sep 2018],

FY19RemainingHours[ PO Hours] - 152)

 

Contract start and end dates vary throughout the year. I need to Subtract 0 if the contract has not yet started and Subtract 0 if the contract has ended. Can I add that to the If statement?

 

I have columns for PO Hours, Sep Hours Worked, Oct Hours Worked (and so on monthly), Start Date, End Date.  I am not familiar working with dates. Can you help me please? I can email a file to you if needed.

9 REPLIES 9
Community Support
Community Support

Re: Contract Start and End Dates calculation for remaining hours

HI @datavis

I need to confirm information with you firstly.

1.

Your condition seems like:

For each row for Sep data,

If [Sep Hours Worked]>0, then [PO Hours]- [Sep Hours Worked],

Else [PO Hours]- 152.

For each row for Oct data,

If [Oct Hours Worked]>0, then [Sep Hours Worked]- [Oct Hours Worked],

Else [Sep Hours Worked]- 152.

 

2.

“Subtract 0 if the contract has not yet started and Subtract 0 if the contract has ended.”

How do you define whether the contract starts or ends?

From your information, it is not clear.

 

You’d better give a dataset example instead of realistic file for data security.

 

Best Regards

Maggie

Highlighted
datavis Resolver I
Resolver I

Re: Contract Start and End Dates calculation for remaining hours

Thank you Maggie for responding. You are correct regarding your understanding of my problem.

I have a column for Start Date and a column for End Date with dates formatted as such:

Start Date          End Date

8/31/2018          10/31/2018

09/01/2018        09/07/2018

09/01/2018        08/31/2019

I am working from an Excel file. Here is a link to a dummy data file.

https://app.box.com/s/f1bcw69d8poeh3z8f32q8qhll4avpevn

 

Community Support
Community Support

Re: Contract Start and End Dates calculation for remaining hours

Hi @datavis

I still don't understand.

what is "Bill" ,"Reported hours" and "final hours"?

your problem is to create a  a burndown report based on the dataset you give, you have alread get the dataset, right?

PO Start Date PO End Date po hours Sept Hours Oct Hours Nov Hours Dec Hours Jan Hours Feb Hours Mar Hours Apr Hours May Hours Jun Hours July Hours Aug Hours
9/1/2018 9/7/2018 2080 Bill Do not bill Do not bill Do not bill Do not bill Do not bill Do not bill Do not bill Do not bill Do not bill Do not bill Do not bill
9/1/2018 8/31/2019 2080 Bill Bill Bill Bill Bill Bill Bill Bill Bill Bill Bill Bill
12/16/2018 8/31/2019 2080 Do not bill Do not bill Do not bill Bill Bill Bill Bill Bill Bill Bill Bill Bill
11/29/2018 8/31/2019 2080 Do not bill Do not bill Bill Bill Bill Bill Bill Bill Bill Bill Bill Bill
3/4/2019 6/9/2019 2080 Do not bill Do not bill Do not bill Do not bill Do not bill Do not bill Bill Bill Bill Bill Do not bill Do not bill
                             
                             
PO Start Date PO End Date po hours Sept Reported Hours Sept Final Hours Start Date Check End Date Check Oct Reported Hours Oct Final Hours            
9/1/2018 9/7/2018 2080 150 1930 1930 1930 0 1930            
9/1/2018 8/31/2019 2080 152 1928 1928 0 160 1768            
12/16/2018 8/31/2019 2080 0 0 2080 0 0 0            
11/29/2018 8/31/2019 2080 0 0 2080 0 0 0            
3/4/2019 6/9/2019 2080 0 0 0 0 0 0            
Ideally the two checks are in the same formula AND would not carry over the starting PO Hours unless hours have been reported.              
datavis Resolver I
Resolver I

Re: Contract Start and End Dates calculation for remaining hours

Hi Maggy,

Sorry I forgot to tell you to ignore those two. Just use the Test Sheet 2 and the available hours on last sheet.

My apologies.

 

Community Support
Community Support

Re: Contract Start and End Dates calculation for remaining hours

Hi @datavis

In your excel, there is no data for month hours.

Assume the cells meet condition" FY19RemainingHours[Sep 2018]<=0" is null

 

In Edit Queries, 

Replace value->Replace null with 0

Add an index column from 1

Unpivot columns ->select all columns from "Sept Hours" till "Aug Hours"

Rename columns, "Attribute"->"month", "Value"->"monthly value"

 

Add custom column "fiscal month" , paste code

each if Text.Contains([month], "Sept") then 1 
else if Text.Contains([month], "Oct") then 2 
else if Text.Contains([month], "Nov") then 3 
else if Text.Contains([month], "Dec") then 4 
else if Text.Contains([month], "Jan") then 5 
else if Text.Contains([month], "Feb") then 6 
else if Text.Contains([month], "Mar") then 7 
else if Text.Contains([month], "Apr") then 8 
else if Text.Contains([month], "May ") then 9 
else if Text.Contains([month], "Jun") then 10 
else if Text.Contains([month], "July") then 11 
else if Text.Contains([month], "Aug") then 12 else null

18.png

 

Close &&Apply,

Go back to Data View

 

Create columns

previous =
IF (
Sheet5[fiscal month] = 1,
[Total PO Hours],
CALCULATE (
MAX ( Sheet5[monthly value] ),
FILTER (
ALLEXCEPT ( Sheet5, Sheet5[Index] ),
[fiscal month]
= EARLIER ( Sheet5[fiscal month] ) - 1
)
)
)

remaining Hours = IF(Sheet5[monthly hours]>0,[previous]-[monthly hours],[previous]-152)
 

19.png

20.png 

 

 

Best Regards

Maggie

 

datavis Resolver I
Resolver I

Re: Contract Start and End Dates calculation for remaining hours

Thank you Maggie. This is an improvement. However, some of the PO's have multiple PO lines by contractor which creates a problem after unpivot - it broke out each contractor multiple times due to the PO line and position status. Please take a look at this excel document of the data expanded to include PO Lines and Contractor names:

https://app.box.com/s/yzg2tjiea7x34gepvjk4dtj1j30kliaj

Also uploaded a pbix fil here:

https://app.box.com/s/j26jq0w3m4ejs5xkq12b39sow0lssj2u

Thank you in advance for taking another look.

Community Support
Community Support

Re: Contract Start and End Dates calculation for remaining hours

Hi @datavis

If you select all columns from "Sept Hours" till "Aug Hours" (not all columns, just hours columns) , then select "unpoviot columns"instead of "Unpivot all columns" or "Unpivot other columns", it finally shows each contractor assciated with the particular PO line and position status.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\ACorwin01\Desktop\Example Data 2.xlsx"), null, true),
    #"Test Sheet 2_Sheet" = Source{[Item="Test Sheet 2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Test Sheet 2_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Position Number", Int64.Type}, {"Contractor", type text}, {"PO", type any}, {"Active or Non-Active", type text}, {"PO Line", Int64.Type}, {"PO Start Date", type date}, {"PO End Date", type date}, {"Total PO Hours", Int64.Type}, {"Sep 2018", type number}, {"Nov 2018", type number}, {"Oct 2018", Int64.Type}, {"Dec. 2018", type any}, {"Jan. 2019", type any}, {"Feb. 2019", type any}, {"Mar. 2019", type any}, {"Apr 2019", type any}, {"May 2019", type any}, {"Jun. 2019", type any}, {"Jul 2019", type any}, {"Aug 2019", type any}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Sep 2018", "Nov 2018", "Oct 2018", "Dec. 2018", "Jan. 2019", "Feb. 2019", "Mar. 2019", "Apr 2019", "May 2019", "Jun. 2019", "Jul 2019", "Aug 2019"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"PO Start Date", "PO End Date", "Position Number", "Contractor", "PO", "Active or Non-Active", "PO Line", "Total PO Hours", "Sep 2018", "Nov 2018", "Oct 2018", "Dec. 2018", "Jan. 2019", "Feb. 2019", "Mar. 2019", "Apr 2019", "May 2019", "Jun. 2019", "Jul 2019", "Aug 2019"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Or replace the code above with the following

#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"PO Start Date", "PO End Date", "Position Number", "Contractor", "PO", "Active or Non-Active", "PO Line", "Total PO Hours","Index"}, "Attribute", "Value")

Best Regards

Maggie

datavis Resolver I
Resolver I

Re: Contract Start and End Dates calculation for remaining hours

Hi Maggie,

I will be out a few more days for holiday so sorry to have not responded. I am trying to show the monthly burndown by contractor in a clustered column chart. The visual below is incorrect because of the problem in which hours available to work are being subtracted when a contract has not yet started or has ended. We only want to subtract hours available to work during the active contract period. Is there a way to show the burndown by month after I unpivot those selected monthly hours columns? See image below.

 

Burndown2.jpgI am using a clustered column chart to show burndown by month and a table below with filters to the right to show burndown by division, department, section and contractor.

datavis Resolver I
Resolver I

Re: Contract Start and End Dates calculation for remaining hours

This was an excellent solution. Thank you!

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors