[Power Query] - M Language

[jajatidev]

Hi,
I have used the following function to populate the week column. However, now I need to offset the week date by bringing it forward by 2 weeks while adhering to the following logic;

1. if the offset week based on CRD is before the current week then it will be the current week
2. if the CRD field is blank then the week filed will always be the current week

if [CRD] is null then Date.StartOfWeek(DateTime.Date(DateTime.LocalNow()),1) else Date.StartOfWeek([CRD],1)

Material	Qty	CRD	Week
A1A30A-KGJ	200	04/26/2024	04/22/2024
1NR73A-K1H	290		04/01/2024
A1A30A-KGJ	250		04/01/2024
5RA32F-KGJ	325	07/26/2024	07/22/2024

 

[jajatidev]

was able to work the function.

if [CRD] is null or Date.AddWeeks(Date.StartOfWeek([CRD],1),-2) < Date.StartOfWeek(DateTime.Date(DateTime.LocalNow()),1) then Date.StartOfWeek(DateTime.Date(DateTime.LocalNow()),1) else Date.AddWeeks(Date.StartOfWeek([CRD],1),-2)