Last twelve months

[peterlyngvig]

Hi,

In the table below, I have presented some monthly cost data - each data represents the sum of the various costs items for each month. The input data is a spreadsheet with credit card transactions with a date column and cost column representing the date and cost for each transaction. I would like to make another version of this table, which shows last twelve months data instead of the monthly data. As an example, the column October, should show the sum of costs for the twelve months starting November the previous year and ending October this year. The column September should show the sum of the costs for the period starting October previous year and ending September this year. And so on. Can anyone help with this please? Thanks 😊

[AnkitKukreja]

Hi @peterlyngvig 

You can try something like this.

Last 12 Months=
CALCULATE (
   [Total Sales],
   DATESINPERIOD (
      'Date'[Date],
      MAX ( 'Date'[Date] ),
      -12,             
      MONTH                 
    )

[peterlyngvig]

Thanks Ankit.

I tried your solution, but I am getting the error message "A single value for column 'Cost' in table 'Sheet1' cannot be determined". See below. What is wrong here? Your help is much appreciated 🙂

[Anonymous]

Hi

If you have a date table it is easy.

12 month =
var datemax=selectedvalue(Date[Month])

return

calculate(sum(cost),removefilters(date[Month)),DATESINPERIOD('Date'[Date],datemax,-11,Month))