Power Query Custom Column Calculation

[PowerRobots99]

Hello Everyone,

I am having weekly data in attached sheet with column ("Report Date" to "Completed Week") which i have imported in Power BI.

Now, i have to develop custom column "In Prior Weeks Schedule?" and "Completed on Schedule?" in Power BI's power query.

(Logic is as explained by using excel formulas below for the calculation).

We have to compare each WO in current reporting week with last reporting week.

Is there any way to achive this?

In Prior Weeks Schedule? =IF(

SUMIFS([WO'#],
[BU],[@BU],
[WO'#],[@[WO'#]],
[Report Week],[@[Report Week]]-1,
[Schedule Week],[@[Report Week]]-1)

=[@[WO'#]],1,0)

Completed on Schedule=IF(

AND([@[In Prior Weeks Scheudle?]],
[@[Completed Week]]=[@[Report Week]]-1),1,0)

[AngeloParana]

Create a custom key and do self join on the previous table.

key is like thise (reportweek-1) + WO#

[PowerRobots99]

@AngeloParana 

Thank you for your reply,

I have uploaded excel file and .pbix file for your reference,

https://drive.google.com/file/d/11Ml22csv7DTZX-Ck16mpQ1u0aTdzDWP7/view?usp=drive_link

https://docs.google.com/spreadsheets/d/1aMBa5f54oMcYgWinr0S69k4jSP1OK4K9/edit?usp=drive_link&ouid=10...

Could you please incorporate solution and share the. pbix file

Thank you,

PowerRobots

[AngeloParana · Accepted solution]

Hi, here you go

PBIX File