"Yes" condition for Previous Week & Next Week if there is Usage for current Week

[Tan_LC]

Spoiler

Spoiler

Hi, I've a table listed with Item, Week of Year & Usage as below.
I would like an "To order" column to be generated.

Criteria:
If Usage <> 0, "To order" = "Yes" for current Week, Previous Week & Next Week, else "No".
 

Item	Week of Year	Usage	To order
a	26	6	Yes
a	27	6	Yes
a	28	3	Yes
a	29	3	Yes
a	30	6	Yes
a	31	6	Yes
b	26	0	No
b	27	0	Yes
b	28	3	Yes
b	29	3	Yes
b	30	3	Yes
b	31	3	Yes
c	26	0	No
c	27	0	No
c	28	0	Yes
c	29	3	Yes
c	30	3	Yes
c	31	0	Yes

Thanks.

Regards,
Tan LC

[Sahir_Maharaj]

Hello @Tan_LC,

Can you please try the following:

To order = 
VAR CurrentWeek = MAX('Table'[Week of Year])
VAR PreviousWeek = CurrentWeek - 1
VAR NextWeek = CurrentWeek + 1
VAR UsageCurrentWeek = CALCULATE(MAX('Table'[Usage]), 'Table'[Week of Year] = CurrentWeek)
VAR UsagePreviousWeek = CALCULATE(MAX('Table'[Usage]), 'Table'[Week of Year] = PreviousWeek)
VAR UsageNextWeek = CALCULATE(MAX('Table'[Usage]), 'Table'[Week of Year] = NextWeek)
RETURN
    IF(
        UsageCurrentWeek <> 0 || UsagePreviousWeek <> 0 || UsageNextWeek <> 0,
        "Yes",
        "No"
    )

[Tan_LC]

@Sahir_Maharaj , thanks on the solution given but sorry that I've missed out on one of the most important criteria:

Criteria:
IF Item = EARLIER(Item) and Usage <> 0, "To order" = "Yes" for current Week, Previous Week & Next Week, else "No".

Regards,

Tan LC