Create a flag for this years' week numbers

[bo_afk]

I have data for 2019 and 2020 to date with the following columns.

Year	WeekNumber	Metric

I would like to create another column with a flag for week numbers of 2020, e.g. for 2020 I have data for weeks 1 to week 17 so put the value '1' for whenever week number is between these for both years and '0' otherwise.

For 2020, flag values should all be '1'.

Desired output

Year	WeekNumber	Metric	Flag
2019	16	#	1
2019	25	#	0

I've created the following formula but it doesn't seem to work.

Flag Column = IF(WeekNumber<= CALCULATE(MAX([WeekNumber]),[Year]=2020),1,0))

Can anyone tell me what's wrong with this formula and how I would fix it?

Thanks!

afk

[parry2k]

@bo_afk not clear what you are looking for, in your sample you have year 2019 and one has flag 1 and other 0, please provide bit more context and details.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[bo_afk]

Hi @parry2k , this was explained in the post above the "desired output" table.

I would like to create another column with a flag for week numbers of 2020, e.g. for 2020 I have data for weeks 1 to week 17 so put the value '1' for whenever week number is between these for both years and '0' otherwise.

If this isn't visible in the post do let me know and I'll try to fix it.

[parry2k]

@bo_afk not clear at all, you have 2019 in your sample data, and flag is set to 1. Maybe I'm not reading it correctly.

[bo_afk]

@parry2k I would like to flag the week numbers by identifying the available weeks for 2020. Since for 2020 we are only at week 17, I would like to flag all rows where week number is between 1 and 17 for 2019 and 2020.

"put the value '1' for whenever week number is between these for both years and '0' otherwise."

Week numbers for 2020 = 1 - 17

Flag = 1 when week number is between 1 and 17

Flag = 0 when week number is not between 1 and 17

[v-yiruan-msft · Accepted solution]

Hi @bo_afk ,

Please update the formula of your calculated column "Flag Column" with the following one:

Flag Column = IF(WeekNumber<= CALCULATE(MAX([WeekNumber]),FILTER('TABLE_NAME',[Year]=2020)),1,0)

Best Regards

Rena

[bo_afk]

Hi @v-yiruan-msft, thanks for your feedback, this has worked perfectly!

Just so I can understand, can you explain why the initial formula didn't work?