Accumulate last 12 month of a formula

[MariaFlorenciaB]

Hi Community!

I am having a problem with a formula and I dont understand why, because it works in others dashboards.

Situation: I have only one database, which has the column measure, value and date. All columns with the correct format.

What I need to do is to accumulate the formula "Turnover" of the last 12 month (It is the Turnover MAT)

And show the Turnover MAT of each month of the last year.

So I did this:

MAT Turnover = CALCULATE([Turnover],ALL('Database'[Fecha]),DATESINPERIOD('Database'[Fecha],LASTDATE('Database'[Fecha]),-12,MONTH))

This formula returns the same number as the formula Turnover.

can someone help me? thanks!!!

[v-alq-msft]

Hi, @MariaFlorenciaB 

I'd like to suggest you create a date table called 'Calendar' with CALENDAR()  or CALENDARAUTO() and then create a relationship with the fact table.

Then you may try the following measure to see if it helps.

MAT Turnover =
CALCULATE (
    [Turnover],
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -12, MONTH )
)

Best Regards

Allan

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

[amitchandak]

@MariaFlorenciaB , Try to use date table in all such case, example like

Rolling 12 = CALCULATE([Turnover],DATESINPERIOD('Date'[Date],ENDOFMONTH(Database[Fecha]),-12,MONTH))
Rolling 12 = CALCULATE([Turnover],DATESINPERIOD('Date'[Date ],MAX(Database[Fecha]),-12,MONTH))
Rolling 12 = CALCULATE([Turnover],DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.