Cumulating values in a calculated column

[ilyakoslov]

Hey guys, I have a problem in power BI that I can't quite understand what I'm doing wrong. What I want to do is to have a column that Sums all the values for each ID for the last 12 months, for that I'm using the code below. However it is giving me the cumulated value for the whole time. Not only the last 12 months. It is as my DATEADD isn't working. 

"Quantidade vendida" is my value and "Material" is my ID, and "Data" is my date. 

Qtd ven 12m = CALCULATE(

    SUM('Base de vendas S'[Quantidade vendida]),

    FILTER('Base de vendas S',

    'Base de vendas S'[Material] = EARLIER('Base de vendas S'[Material]) &&

    'Base de vendas S'[Data] >= DATEADD('Base de vendas S'[Data],-12,MONTH) &&

    'Base de vendas S'[Data] <= EARLIER('Base de vendas S'[Data])))

[ichavarria]

Hi @ilyakoslov,

It looks like the problem might be with the use of the EARLIER function in your code. The EARLIER function is used to reference a previous row context in the current context, but in this case, it might not be working as intended.

To get the sum of the last 12 months for each ID, you can try using the CALCULATETABLE function with the FILTER function to filter the data to the last 12 months and the SUMMARIZE function to group the data by ID. Here's an example of the code you can use:

Qtd ven 12m =

CALCULATE(

SUM('Base de vendas S'[Quantidade vendida]),

FILTER(

SUMMARIZE(

'Base de vendas S',

'Base de vendas S'[Material],

"Last 12 Months",

DATESBETWEEN('Base de vendas S'[Data],

NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Base de vendas S'[Data]))),

LASTDATE('Base de vendas S'[Data])) ),

'Base de vendas S'[Material] = 'Base de vendas S'[Material] &&

'Base de vendas S'[Data] >= [Last 12 Months]

)

)

This code creates a table that groups the data by ID and the last 12 months using the SUMMARIZE function and the DATESBETWEEN function. It then filters the table to the last 12 months for each ID using the FILTER function and the calculated column "Last 12 Months" from the SUMMARIZE function.

I hope this helps! Let me know if you have any further questions.

Isaac Chavarria

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