Forum Discussion
Last 12 Month Extract
Hello,
I'm new to PBI.
I have a table with many columns including Year and Month. I need to SUMMARIZE table into another table with only last 12 months data. We do not have DATE Dimmension in the model. Also this table does not have current month data, it has upto last month data. IN my current table max month is Oct. I need to just get Year and Month Going back to Nov
Year Month
2018 6
2018 6
2018 6
2018 6
2018 6
2018 7
2018 7
and so on
Perhaps something like:
Table 1 = VAR __year = MAX('Table'[Year]) VAR __yearPrev = __year - 1 VAR __month = MAXX(FILTER('Table',[Year]=__year),[Month]) VAR __table = FILTER('Table',[Year] = __year || ([Year] = __yearPrev && [Month] > __month)) RETURN SUMMARIZE(__table,[Year],[Month],"My Column",SUM([Value]))Also, I feel that this link might come in handy for you:
4 Replies
Perhaps something like:
Table 1 = VAR __year = MAX('Table'[Year]) VAR __yearPrev = __year - 1 VAR __month = MAXX(FILTER('Table',[Year]=__year),[Month]) VAR __table = FILTER('Table',[Year] = __year || ([Year] = __yearPrev && [Month] > __month)) RETURN SUMMARIZE(__table,[Year],[Month],"My Column",SUM([Value]))Also, I feel that this link might come in handy for you:
Thanks
VAR __month = MAXX(FILTER('Table',[Year]=__year),[Month]) -- This row is not letting meselect Month from the table
Make sure to replace 'Table' with the name of your actual table. Does your table have a Month column? I have this working in a model, you want "Table 1" in the attached file. This builds a table based upon the "Calendar" table.
