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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
v-juanli-msft
Community Support
Community Support

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

 

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.

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

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.

 

I 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.I 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.

This was an excellent solution. Thank you!

 

v-juanli-msft
Community Support
Community Support

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

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

 

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.              

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.

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.