Reiniciar acumulado por determinado numero de meses

[telesforo1969]

Qué código le agrego a mi fórmula para que reinicie el contador, en este caso cada 12 meses.

Gracias por el apoyo. La fórmula de acumulado funciona correctamente pero quiero que reinicie en un determinado número de meses. para el ejemplo que presento en Marzo del 2021 debería volver a iniciar el acumulado.

Tengo tabla Calendario

Acumulado Ventas 2 =

VAR MaxFecha = MAXX(ALLSELECTED(Calendario),Calendario[Date])

VAR MinFecha = MINX(ALLSELECTED(Calendario),Calendario[Date])

Var A**bleep**ulado =

    CALCULATE([Ventas],

    FILTER(ALLSELECTED(Calendario), Calendario[Date]<= MAX(Calendario[Date]) && Calendario[Date] >= MinFecha && Calendario[Date] <= MaxFecha))

RETURN

Acumulado

[v-jingzhan-msft · Accepted solution]

Hi @telesforo1969 

Maybe you want something like this. We need to adjust the start date of each period to start a new running total. And if we change the start date, it will calculate the running total based on the new start date for the next 12 months. 

Accumulative Total = 
var firstStartDate = MINX(ALLSELECTED('calendar'),'calendar'[Date])
var curYear = MAX('calendar'[Year])
var startDate = DATE(IF(MAX('calendar'[Month])>=MONTH(firstStartDate),curYear,curYear-1), MONTH(firstStartDate), DAY(firstStartDate))
var endDate = EOMONTH(startDate,11)
RETURN
CALCULATE([Total],'calendar'[Date]>=startDate && 'calendar'[Date]<=endDate && 'calendar'[Date]<=MAX('calendar'[Date]))

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[telesforo1969 · Accepted solution]

Thank you so much. 

[telesforo1969]

Your skills are excellent

[telesforo1969 · Accepted solution]

Muchas gracias, eres inteligente.

[telesforo1969 · Accepted solution]

Thank you so much. 

[v-jingzhan-msft]

Hi @telesforo1969 

Do you have a fiscal year column in your calendar table? If not, you can first add a calendar year column to it like below.

DAX: 

Fiscal Year = IF(MONTH([Date])<3, YEAR([Date])-1, YEAR([Date]))

Then add a filter on fiscal year to your current measure like below

Acumulado Ventas 2 =

VAR MaxFecha = MAXX(ALLSELECTED(Calendario),Calendario[Date])
VAR MinFecha = MINX(ALLSELECTED(Calendario),Calendario[Date])

Var Acumulado =
    CALCULATE([Ventas],
    FILTER(ALLSELECTED(Calendario), Calendario[Date]<= MAX(Calendario[Date]) && Calendario[Date] >= MinFecha && Calendario[Date] <= MaxFecha && Calendario[Fiscal Year] = MAX(Calendario[Fiscal Year])))
RETURN
Acumulado

In addition, if the year-end month is not February, there is an easier method to calculate the running total with DATESYTD function as this function can define a different year-end date. However as in your case the year-end month is Febaruay, the year-end date is "2-29" in leap years and "2-28" in other years, it seems not work very well with DATESYTD. Computing running totals in DAX - SQLBI

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[telesforo1969]

No creo que necesite un anio fiscal, sólo necesito indicar cuántos meses después quiero reiniciar el acumulado a partir de una fecha inicial.

Acumulado Ventas 6 =

VAR MaxFecha = MAXX(ALLSELECTED(Calendario),Calendario[Date])

VAR MinFecha = MINX(ALLSELECTED(Calendario),Calendario[Date])

VAR ReiniciarAcumulado = EOMONTH(MinFecha,+11) // This is the number of months in which I want the monthly cumulative to reset, I don't know if the syntax to reset cumulative is correct

Var Acumulado =

    CALCULATE([Ventas],

    FILTER(ALLSELECTED(Calendario), Calendario[Date]<= MAX(Calendario[Date]) && Calendario[Date] >= MinFecha && Calendario[Date] ) ) //How do I code that every 11 months I restart the accumulated in this period

RETURN

Acumulado

[telesforo1969]

[v-jingzhan-msft · Accepted solution]

Hi @telesforo1969 

Maybe you want something like this. We need to adjust the start date of each period to start a new running total. And if we change the start date, it will calculate the running total based on the new start date for the next 12 months. 

Accumulative Total = 
var firstStartDate = MINX(ALLSELECTED('calendar'),'calendar'[Date])
var curYear = MAX('calendar'[Year])
var startDate = DATE(IF(MAX('calendar'[Month])>=MONTH(firstStartDate),curYear,curYear-1), MONTH(firstStartDate), DAY(firstStartDate))
var endDate = EOMONTH(startDate,11)
RETURN
CALCULATE([Total],'calendar'[Date]>=startDate && 'calendar'[Date]<=endDate && 'calendar'[Date]<=MAX('calendar'[Date]))

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!