Calculated column to SUM last 12 months of sales BY ACCOUNT number

[gingerclaire]

I have data that looks like the below:

Date	Product	Account number	Value
1 april 2024	Product 1	1	£5
1 april 2024	Product 1	2	£5
1 april 2024	Product 2	1	£5
1 april 2024	Product 3	1	£5

• I need to add a calculated column to sum all sales in the last 12 months BY account number.
  My sales data is often a month old - so I need the calculation to be based on the 12 months before the most recent  date in the table
• So I want it to look like the below.....as a column in my data table and not in a visual

Date	Product	Account number	Value	Sales in last 12 months
1 april 2024	Product 1	1	£5
1 april 2024	Product 1	2	£5
1 april 2024	Product 2	1	£5
1 april 2024	Product 3	1	£5

[suparnababu8]

Hi @gingerclaire 

You need to create a calculate columns as mentioned below.'

SalesLast12Months = 
VAR CurrentDate = 'Table'[Date]
VAR AccountNumber = 'Table'[Account number]
VAR Last12Months = 
    CALCULATE(SUM('Table'[Value]),
        FILTER(
            'Table',
            'Table'[Account number] = AccountNumber &&
            'Table'[Date] <= CurrentDate &&
            'Table'[Date] > EDATE(CurrentDate, -12)
        )
    )
RETURN Last12Months

The use this calculated columns in your matrix/table chart.

If you need additional info pls follow the below URLs

https://community.fabric.microsoft.com/t5/Desktop/DAX-Calculate-sum-of-last-12-months-from-each-mont...

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Accumulative-Sum-of-Sales-...

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/12-Previous-Months-Sum-for-each-mont...

Hope this helps you!

Thanks!

[Kishore_KVN]

Hello @gingerclaire ,

Please follow below steps to achieve your requirement.

step1: Please create a calendar table

step2: Create relationship between your calendar table date and sales table date columns

step3: Create a new measure with below DAX:

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

Step4: Drag your measure into the table visual to get the required output. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[gingerclaire]

Hi - I want to have this as a column in my data table and not in a visual. Will this work too?

[Anonymous · Accepted solution]

Hi @gingerclaire ,

When you choose to create a Calculated column, it enables it to be a column in your datasheet rather than in the view.

I also created a table similar to yours, but for the convenience of the DAX code, I chose to convert £5 to 5. If you want the original format, you can use the FORMAT function.

Then I think you can create a calculated column as suparnababu8 mentioned.

Column = 
VAR LatestDate = MAX('Table'[Date])
VAR StartDate = EDATE(LatestDate, -12)
RETURN
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Account number] = EARLIER('Table'[Account number]) &&
        'Table'[Date] >= StartDate &&
        'Table'[Date] <= LatestDate
    )
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.