Best approach to creating table visual

Anonymous ,

Ok, you need a proper calendar table in your model. This will help enormously with the time intelligence you're trying to use, but also will isolate your date evaluations from context filters on your report.

Once you have your calendar table, then you can add a monthNumber column, like this:

//DAX
monthNumber = MONTH(calendarTable[Date])

//M
Date.Month([Date])

Then add a relativeMonth column, like this:

//DAX
(YEAR(calendar[Date]) * 12 + calendar[monthNumber])
- (YEAR(TODAY()) * 12 + MONTH(TODAY()))

//M
(Date.Year([Date]) * 12 + [monthNumber])
- (Date.Year(Date.From(DateTime.LocalNow())) * 12 + Date.Month(Date.From(DateTime.LocalNow())))

Relate your calendar table to your fact table on calendar[Date] ONE : MANY Data[Reference_Date].

You should then be able to write a generic measure, something like this:

Sales LQ =
CALCULATE(
  SUM(Data[Sales]) / 1000000,
  calendar[relativeMonth] <= -1
)

When you put that measure into your table, just filter the visual on [Area] = "Alpha" etc. and the date evaluation should be insulated from the filtering of your fact table.

Pete