How to get group of values by year and month

[andynedine]

Hi!!

I have a table with this kind of values:

Year	Month	Value
2018	11	2
2018	12	3
2019	10	3
2019	11	4
2019	12	5
2020	10	1
2020	11	4
2020	12	5
2020	12	2
2021	1	3
2021	2	6
2021	3	2
2021	3	1
2021	3	5

I can't find a solution to make a "sub-table" or filter it to get only that values are from a year and the last month of that year. The final result must be:

Year	Month	Value
2018	12	3
2019	12	5
2020	12	5
2020	12	2
2021	3	2
2021	3	1
2021	3	5

I got the table with de last values, but without other years (only from 2021):

LastValues = 
var table = FILTER(
    RTA,
    AND( 
        MAX('Company'[Year])='Company'[Year],
        MAX('Company'[Month.Number])='Company'[Month.Number]
    )
)
return SUMMARIZE(table, Company[Year], Company[Month.Number], Company[Dept], Company[Nº Dept], Company[Nº Empl])

How I would be able to keep the last month of the rest of years??

[andynedine · Accepted solution]

Finally, I solved my problem with this code:

Summary = 
var tabla2 = FILTER(
    Table,
    IF('Table'[Year]=YEAR(NOW()),
    'Table'[Month.Number]=calculate(MAX(Table[Month.Number]),FILTER(Table,'Table'[Year]=YEAR(NOW()))),
    MAX('Table'[Month.Number])='Table'[Month.Number])
)
return SUMMARIZE(tabla2, Table[Year], Table[Month.Number], Table[Dept], Table[Nº Dept], Table[Nº Empl])

Althouth I could find this one too:

Summary = 
VAR vYear =
    VALUES ( Table[Año] )
VAR vYearMaxMonth =
    ADDCOLUMNS ( vYear, "@MaxMonth", CALCULATE ( MAX ( Table[Month.Number] ) ) )
VAR vYearMaxFilter =
    TREATAS ( vYearMaxMonth, Table[Year], Table[Month.Number] )
VAR vResult =
    CALCULATETABLE ( Table, vYearMaxFilter )
RETURN
    vResult

[Jihwan_Kim]

Hi @andynedine 

Please try the below, and please let me know if it is not working.

Outcome =

VAR maxmonthnumber = CALCULATE( max( 'Table'[Month]), ALLEXCEPT( 'Table', 'Table'[Year]))

RETURN

CALCULATE( SELECTEDVALUE('Table'[Value]), 'Table'[Month] = maxmonthnumber)

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos!!

[andynedine]

Thanks a lot for your answer, but, unfortunately, it returns an error like "this table is not a valid expression" ("La expresión especificada en la consulta no es una expresión de tabla válida." because I use spanish version).

I tried to modify your answer:

Outcome = 
var maxmonthnumber = CALCULATE( max( 'Table'[Month.Number]), ALLEXCEPT( 'Table', 'Table'[Year]))

var tabla = FILTER(
    RTA,
    CALCULATE( SELECTEDVALUE('Table'[Nº Dept]), 'Table'[Month.Number] = maxmonthnumber)
)

return SUMMARIZE(tabla, Table[Year], Table[Month.Number], Table[Company], Table[Nº Dept], Table[Nº.Empl])

But I got correct values.... less 2021.

Year	Month	Value
2018	12	3
2019	12	5
2020	12	5
2020	12	2

[V-lianl-msft]

Try to create a new table like this:

Table 2 = 
var current_year = YEAR(TODAY())
var max_month = 12
var not_c_y = FILTER('Table','Table'[Year]<>current_year&&'Table'[Month]=max_month)
var c_y = FILTER('Table','Table'[Year]=current_year)
return UNION(c_y,not_c_y)