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
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.
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
157 | |
137 | |
132 | |
81 | |
61 |