Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
telesforo1969
Helper III
Helper III

Reiniciar acumulado por determinado numero de meses

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

 

Reiniciar acumulado.JPG

3 ACCEPTED SOLUTIONS

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

vjingzhanmsft_0-1713519043701.png

vjingzhanmsft_1-1713519095670.png

 

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

View solution in original post

telesforo1969
Helper III
Helper III

Thank you so much. 

 

Reiniciar 1.JPG

 

View solution in original post

telesforo1969
Helper III
Helper III

Muchas gracias, eres inteligente.

Reiniciar 2.JPG

View solution in original post

7 REPLIES 7
telesforo1969
Helper III
Helper III

Your skills are excellent

 

Reiniciar 2.JPG

telesforo1969
Helper III
Helper III

Muchas gracias, eres inteligente.

Reiniciar 2.JPG

telesforo1969
Helper III
Helper III

Thank you so much. 

 

Reiniciar 1.JPG

 

v-jingzhan-msft
Community Support
Community Support

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

vjingzhanmsft_0-1713417169483.png

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!

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

Reiniciar.JPG

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

vjingzhanmsft_0-1713519043701.png

vjingzhanmsft_1-1713519095670.png

 

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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