Create a Calculated Column (or one-column table) as VAR using criteria.

[_fmigg]

Hi, 

I'm trying to create a measure that indicates with a 1 or 0 based on the following data: 

Year	Month
2019	Jan
2019	Feb
2019	Mar
2019	Apr
2019	May
2019	Jun
2019	Jul
2019	Aug
2019	Sep
2019	Oct
2019	Nov
2019	Dec
2020	Jan
2020	Feb
2020	Mar

If the Month exists in the MAX Year, I will return a 1, otherwise 0. 

So the measure should work like this if is added to a visual table: 

Year	Month	isMonthInMaxYear
2019	Jan	1
2019	Feb	1
2019	Mar	1
2019	Apr	0
2019	May	0
2019	Jun	0
2019	Jul	0
2019	Aug	0
2019	Sep	0
2019	Oct	0
2019	Nov	0
2019	Dec	0
2020	Jan	1
2020	Feb	1
2020	Mar	1

I can't solve it statically because at the future there will be more records on the table and the 1's will be changing over time. 
I can't create more tables at the data model (nor calculated tables or Power Query tables).

What I'm trying to do is to have a table a one-column table as VAR on the measure, but I'm kind of stuck because VALUES function doesn't accept criteria, SUMMARIZECOLUMNS asks for an index column in which criteria can't be applied and CALCULATE TABLE will return the table with Year and Month columns. 

I'd like to have a table with the months of the highest year in a VAR, so the table would look like this. 

Month
Jan
Feb
Mar

And then use it on a condition using the IN function, but I'm stuck on the previous step.

Is it possible to do that? Is this approach correct? 

Thanks

[v-lili6-msft]

hi, @_fmigg 

You could try this formula logic to create a measure:

Measure = var _maxyear=CALCULATE(MAX('Table'[Year]),ALLSELECTED('Table'))
var _table=CALCULATETABLE(VALUES('Table'[Month]),FILTER(ALLSELECTED('Table'),'Table'[Year]=_maxyear)) return
IF(SELECTEDVALUE('Table'[Month]) IN _table,1,0)

of course, you could use ALL instead of ALLSELECTED in the formula

and here is my sample pbix, please try it.

Best Regards,

Lin