I'm trying to create a measure that indicates with a 1 or 0 based on the following data:
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, neither 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.
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?