Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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)
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.
This was an excellent solution. Thank you!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |