Last 12 Month Extract

[sabeensp]

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

[Greg_Deckler · Accepted solution]

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:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[sabeensp]

Thanks

VAR __month = MAXX(FILTER('Table',[Year]=__year),[Month]) -- This row is not letting meselect Month from the table

[Greg_Deckler]

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.

[sabeensp]

Thanks, I missed typed your MAXX with MAX,