topic Re: Last twelve months in DAX Commands and Tips

Last twelve months

peterlyngvig — Sat, 12 Nov 2022 15:19:48 GMT

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 😊

Re: Last twelve months

Anonymous — Sat, 12 Nov 2022 16:55:22 GMT

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))

Re: Last twelve months

AnkitKukreja — Sat, 12 Nov 2022 16:58:27 GMT

Hi peterlyngvig

You can try something like this.

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

Re: Last twelve months

peterlyngvig — Sat, 12 Nov 2022 19:50:03 GMT

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 🙂