cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

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

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

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

3 ACCEPTED SOLUTIONS
Community Support

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!

Helper III

Thank you so much.

Helper III

Muchas gracias, eres inteligente.

7 REPLIES 7
Helper III

Helper III

Muchas gracias, eres inteligente.

Helper III

Thank you so much.

Community Support

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

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

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!

Helper III

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.

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

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
Helper III

Community Support

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!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors