Forum Discussion

peterlyngvig's avatar
peterlyngvig
Regular Visitor
3 years ago

Last twelve months

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 ðŸ˜Š

 

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

  • AnkitKukreja's avatar
    AnkitKukreja
    Icon for Super User rankSuper User
    3 years ago

    Hi peterlyngvig 

     

    You can try something like this.

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

     

    • peterlyngvig's avatar
      peterlyngvig
      Regular Visitor
      3 years ago

      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 🙂