Excel logic to be used in Power BI

[samioberoi]

Hi everyone!

I have been given this excel work where there is this excel formula used to calculate the capacity of resouces and this logic has to be used in the Power BI.

=IF(AND(MONTH(XLOOKUP($A1,TABLE_A!$B:$B,TABLE_A!$S:$S))=MONTH(TABLE_B!$D$1),YEAR(XLOOKUP($A1, TABLE_A! $B:$B, TABLE_A!$S:$S)) = YEAR(TABLE_B!$D$1)), NETWORKDAYS(TABLE_B!$D$1, XLOOKUP($A1, TABLE_A! $B:$B, TABLE_A!$S:$S)), 

IF(AND(MONTH(XLOOKUP($A1,TABLE_B!TABLE_A!$B:$B,TABLE_A!$J:$J))=MONTH(TABLE_B!$D$1),YEAR(XLOOKUP($A1, TABLE_A! $B:$B, TABLE_A!$J:$J)) = YEAR(TABLE_B!$D$1)), NETWORKDAYS(XLOOKUP($A1, TABLE_A! $B:$B, TABLE_A!$J:$J), TABLE_B!$E$1),

IF(AND(MONTH(XLOOKUP($A1,TABLE_A!$B:$B,TABLE_A!$S:$S)<>"", XLOOKUP($A1,TABLE_A!$B:$B,TABLE_A!$S:$S)< TABLE_B!$D$1),0,

SUM(TABLE_B!$D$4*($C81/100))))

Using the filtering between the tables, it may be very easy, but i am not clear with the understanding of the formula above used in the excel.  Can someone help me on this please?

Thanks

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[samioberoi]

Hi Ibendlin,

I have tried to attach both Excel & PBI file links. I don't know if these will work, but this is to my best ability i could upload these as it doesn't let me here to attach these. Please let me know if you are able to open these or if there is any other way for me to send these out for you.

 https://1drv.ms/u/c/6d7b5a2cb301fa95/ESwzq8JmlIpPs1s71jBWAxcBBFrhG0QsHGg4HAMpb3h2rw?e=eB3coZ 

DUMMY DATA.xlsx

 https://onedrive.live.com/?id=root&cid=6D7B5A2CB301FA95#:~:text=%3Ciframe%20src%3D%22https%3A//1drv.... 

In excel file the way it is calculated in Table B in column called CAPS e.g in row 4 ignoring all the FALSE values, that is the way i would like it to be calculated in Power BI without this complex excel formula. 

Thanks for your response and please let me know if it doesn't let you open the files throughs these shared links.

Regards

[lbendlin]

Thank you for the sample data.  Can you describe in business terms what you are trying to calculate? Is it the "Column5" in TABLE_A or the CAPS in TABLE_B?

[samioberoi]

Hi Ibendlin,

Thank you for your reply. It is CAPS column in TABLE_B where there is that big excel formula used as showing in the first post here. It has been used to calculate the capacity of the resources e.g from the Table A here based on a few conditions from all tables of Table A, Table B and CALCS table. It may be very easy to get the same results in the Power BI through filtering between the tables and by using the Date dimension table.

In simple terms, the way it should work as e.g. if there is no date in EARLIEST FROM DATE and LATEST AVAILABLE TO DATE columns in Table A or if these are blanks, then it should give the normal Network days e.g for April it should show Network days as 22 and for May it should show 23 and so on for the resources where these two date columns are blank, but for the resourceID's where they have the dates in either the EARLIEST FROM DATE and LATEST AVAILABLE TO DATE columns in Table A, then it should calculate the Network days after the EARLIEST FROM DATE or before the LATEST AVAILABLE TO DATE for that resource for that specific month e.g. Let's say a resource's LATEST AVAILABLE TO DATE is showing today 26th June 2024, so it should calculate the Network days for this month starting from the first of this month till today which will be 18 Network days. Similarly e.g if the resource's EARLIEST FROM DATE is today 26th June 2024, then it should calculate Network days from today till the end of this month, which will be only 3 Network days.

I will try my best if you need some more information on this and thank you again for your effort in trying to help me out on this.

Best regards