I have a table with two columns, one for Month and one for Year. I have created a summary table based on connected tables, but need to create columns based on month/year combination. For example, one column will show a sum of a value for 3 months prior, 6 months prior, etc...
My question is how can i create a FILTER that takes the month / year columns into considertation.
For example, I want to see 10 months back from the current day. The way i have it now is, Month(utcnow()) - 10, which equals -1. I need this to equal 12 (December). It would also need to account for the year being different.
It looks like @Nathaniel_C set you up with a solution using M. Do you have a date table set up? Might be useful to have for this time intellegence type stuff. You could have a column in your date table that looks something like this:
"YearMonthNumber", FORMAT ( [Date], "YYYY.MM" )
Then, as long as all your tables are related to the date table, you can use the date table to establish your filters. Key field should just be date.
Then you can adjust your times using DATEADD
Sales minus 3 months =CALCULATE([Sales], DATEADD(DateTable[Date],-3, MONTH))
Kind of a hodge podge answer from me but if it works, please mark it as a solution or let me know if you have more questions on this technique.
Do these for each of the different time intelligence periods you might want.
Then, for each of your measures used to set your summary table information, they are more legible:
Sales Rolling 10 Months =CALCULATE([Sales], [Rolling 10 Months]="TRUE")
The advantage here is that the convenience filters aren't just beneficial to your summary table. They also help in other visuals, as you can add the filter to a visual containing a Measure, make it so the data is filtered to where [Convenience Filter] = "TRUE", and automagically restrict the dates used in that visual.