The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Pbix community
I have 2 excels file and created a formula to match different columns to see how many months we need to reduce from
Go live date to start working on the project.
One file is called Capacity File
Capacity File | |||||||||||
Region | Country | Country ID | Capacity EEs Per Entity | Startup 1-5 | Existing 1-5 | Existing 6-30 | Existing 31-100 | Existing 101-200 | Existing 201-500 | Existing 501-1000 | Fiscal Year DD/MM |
AMER | Antigua & Barbuda | AG | 100 | 3 | 3 | 4 | 4 | NA | NA | 01/01 to 31/12 | |
AMER | Argentina | AR | 1000 | 4 | 4 | 4 | 4 | 5 | 6 | 7 | 01/01 to 31/12 |
AMER | Aruba | AW | 100 | 4 | 4 | 4 | 4 | 4 | 5 | 5 | 01/01 to 31/12 |
AMER | Bahamas | BS | 200 | 3 | 3 | 4 | 4 | 5 | NA | NA | 01/01 to 31/12 |
AMER | Bermuda | BM | 200 | 2 | 2 | 3 | 3 | 4 | NA | NA | 01/01 to 31/12 |
AMER | Bolivia | BO | 200 | 3 | 3 | 4 | 4 | 5 | NA | NA | 01/01 to 31/12 |
AMER | Brazil | BR | 1000 | 4 | 4 | 4 | 4 | 4 | 5 | 5 | 01/01 to 31/12 |
AMER | British Virgin Islands | VG | 100 | 3 | 3 | 4 | 4 | 5 | NA | NA | 01/01 to 31/12 |
AMER | Canada | CA | 1000 | 4 | 4 | 4 | 5 | 5 | 5 | 5 | 01/01 to 31/12 |
AMER | Trinidad & Tobago | TT | 100 | 3 | 3 | 4 | 4 | NA | NA | NA | 01/01 to 31/12 |
AMER | United States | US | 1000 | 4 | 4 | 4 | 4 | 4 | 5 | 7 | 01/01 to 31/12 |
AMER | Uruguay | UY | 1000 | 3 | 3 | 4 | 4 | 4 | 5 | 5 | 01/01 to 31/12 |
AMER | Venezuela | VE | 1000 | 4 | 4 | 4 | 5 | 5 | 7 | 7 | 01/01 to 31/12 |
EMEA | France | FR | 1000 | 4 | 4 | 4 | 5 | 5 | 5 | 8 | 01/01 to 31/12 |
EMEA | Georgia | GE | 1000 | 2 | 2 | 3 | 4 | 5 | 6 | 7 | 01-01 to 31-12 |
EMEA | Germany | DE | 1000 | 3 | 3 | 3 | 3 | 3 | 4 | 4 | 01/01 – 31/12 |
EMEA | Ghana | GH | 1000 | 2 | 2 | 2 | 3 | 3 | 4 | 5 | 01/01 to 31/12 |
EMEA | Greece | GR | 1000 | 2 | 3 | 3 | 4 | 5 | 6 | 6 | 01/01 to 31/12 |
EMEA | Guernsey | GG | 250 | 4 | 4 | 4 | 4 | 7 | NA | NA |
And the other one is the Slippage Report (last column is the number from the Capacity File that I use to reduce from column Actual/Schedule Go Live Date)
The formula I use in Excel is the one below:
=INDEX('Capacity Book'!$C$2:$Q$203,MATCH(G31,'Capacity Book'!$C$2:$C$203,0),HLOOKUP(VLOOKUP(I31,'Capacity Book'!V:W,2,0),'Capacity Book'!$C$2:$M$203,2,0))
but having issues with PowerBI
Work Region | Country | Project type | headcount | Expected start date of the work effort (YYYY-MM-DD) | Actual/Schedule Go Live date (YYYY-MM-DD) | Standard impl duration according to Capacity File |
EMEA | France | New business | 180 | 12/2/2024 | 4/1/2025 | 5 |
AMER | United States | New business | 26 | 12/1/2024 | 3/2/2025 | 4 |
EMEA | Germany | New business | 625 | 11/1/2024 | 2/1/2025 | 4 |
AMER | Canada | New business | 10 | 11/1/2024 | 3/2/2025 | 4 |
EMEA | Cyprus | New business | 9 | 4/21/2025 | 7/1/2025 | 2 |
EMEA | Germany | New business | 19 | 6/20/2025 | 10/1/2025 | 3 |
For example...
in the Slippage Report first row Is
Region= EMEA
Country=France
Headcount 180
Go live Date = 04/01/2025 (MM-DD-YYYY)
In the Capacity File For EMEA, France and Column "Existing 101-200" is 5. Meaning we need to start 5 months before the Go Live Date (December 2024)
WE use "Existing 101-200" becasue the Headcount for this project is 180.
Solved! Go to Solution.
Hi @romovaro ,
I was planning to use lookupvalue to retrieve the headcount number from the SlippageReport table to the CapacityFile, using the 'Country' field as the identifier key. However, the duplicate 'Country' entries in the SlippageReport table are preventing me from doing that. Should there be duplicates in this context? Is this duplication due to the fact that there are multiple legal entities in Germany?
Due to this duplication, I took a different approach and brought the standard implied duration from the CapacityFile to the SlippageReport table by writing the following DAX formula.
StandardImplDuration =
SWITCH(
TRUE(),
SlippageReport[headcount] <= 5,
LOOKUPVALUE(
CapacityFile[Existing 1-5],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 30,
LOOKUPVALUE(
CapacityFile[Existing 6-30],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 100,
LOOKUPVALUE(
CapacityFile[Existing 31-100],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 200,
LOOKUPVALUE(
CapacityFile[Existing 101-200],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 500,
LOOKUPVALUE(
CapacityFile[Existing 201-500],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 1000,
LOOKUPVALUE(
CapacityFile[Existing 501-1000],
CapacityFile[Country], SlippageReport[Country]
),
BLANK() -- Returns BLANK if none of the conditions are met
)
Since Cyprus is not in the CapacityFile table, the row output for Cyprus remains blank instead of showing 2, but the other outputs are in line with your expectations.
I have attached an example pbix file for your reference.
Best regards,
Hi @romovaro ,
I was planning to use lookupvalue to retrieve the headcount number from the SlippageReport table to the CapacityFile, using the 'Country' field as the identifier key. However, the duplicate 'Country' entries in the SlippageReport table are preventing me from doing that. Should there be duplicates in this context? Is this duplication due to the fact that there are multiple legal entities in Germany?
Due to this duplication, I took a different approach and brought the standard implied duration from the CapacityFile to the SlippageReport table by writing the following DAX formula.
StandardImplDuration =
SWITCH(
TRUE(),
SlippageReport[headcount] <= 5,
LOOKUPVALUE(
CapacityFile[Existing 1-5],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 30,
LOOKUPVALUE(
CapacityFile[Existing 6-30],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 100,
LOOKUPVALUE(
CapacityFile[Existing 31-100],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 200,
LOOKUPVALUE(
CapacityFile[Existing 101-200],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 500,
LOOKUPVALUE(
CapacityFile[Existing 201-500],
CapacityFile[Country], SlippageReport[Country]
),
SlippageReport[headcount] <= 1000,
LOOKUPVALUE(
CapacityFile[Existing 501-1000],
CapacityFile[Country], SlippageReport[Country]
),
BLANK() -- Returns BLANK if none of the conditions are met
)
Since Cyprus is not in the CapacityFile table, the row output for Cyprus remains blank instead of showing 2, but the other outputs are in line with your expectations.
I have attached an example pbix file for your reference.
Best regards,
Hi @romovaro - hope you already have a relationship between the Region and Country columns from both tables.
You will use DAX to mimic your Excel formula in Power BI. create a measure as below:
Months_to_Subtract =
VAR RegionVal = 'SlippageReport'[Work Region]
VAR CountryVal = 'SlippageReport'[Country]
VAR Headcount = 'SlippageReport'[headcount]
RETURN
CALCULATE(
SWITCH(
TRUE(),
Headcount <= 5, LOOKUPVALUE('CapacityFile'[Startup 1-5], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 30, LOOKUPVALUE('CapacityFile'[Existing 1-5], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 100, LOOKUPVALUE('CapacityFile'[Existing 6-30], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 200, LOOKUPVALUE('CapacityFile'[Existing 31-100], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 500, LOOKUPVALUE('CapacityFile'[Existing 101-200], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount <= 1000, LOOKUPVALUE('CapacityFile'[Existing 201-500], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
Headcount > 1000, LOOKUPVALUE('CapacityFile'[Existing 501-1000], 'CapacityFile'[Region], RegionVal, 'CapacityFile'[Country], CountryVal),
BLANK()
)
)
Now, create another calculated column in the SlippageReport that adjusts the Go Live Date based on the slippage months retrieved in the previous step.
Adjusted_Start_Date =
'SlippageReport'[Actual/Schedule Go Live date] -
( [Months_to_Subtract] * 30 ) -- Assuming an average of 30 days per month
hope this helps in your calculation to find the capacity.
Proud to be a Super User! | |