Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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??
Solved! Go to 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
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
Hi @andynedine
Please try the below, and please let me know if it is not working.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
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)