cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

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!

Resolver I

Thank you so much.

Resolver I

Muchas gracias, eres inteligente.

7 REPLIES 7
Resolver I

Resolver I

Muchas gracias, eres inteligente.

Resolver I

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!

Resolver I

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
Resolver I

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors