Help - Cummulative measure - doesent restart, w/ no date field

[frenst]

Hello friends,

I have problem, I am constructing a measure that cumulate the sum of something, with this formula, BUT it doesent restart with every year:

ACUM META = 
CALCULATE(
	[MC_CANT VENTAS PRESUPUESTADAS POR CATEGORIA];
	FILTER(
		ALL(DIM_PERIODO_COMERCIAL);
		DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL]<=MAX(DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL])))

 I am working with Direct Query. In my "time" dimension I dont have a date field.

An example of my "time" dimension:

Thanks a lot if you can help me

Cheers 

Fred

[v-lid-msft]

Hi @frenst ,

We can use following measure to meet your requirement:

ACUM META =
CALCULATE (
    [MC_CANT VENTAS PRESUPUESTADAS POR CATEGORIA];
    FILTER (
        ALL ( DIM_PERIODO_COMERCIAL );
        DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL]
            <= MAX ( DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL] )
            && DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL]
                >= ROUNDDOWN ( MAX ( DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL] ) / 100; 0 ) * 100 + 1
    )
)

Best regards,

[Ashish_Mathur · Accepted solution]

Hello

Enter a calculated column formula to create a Date field. This can also be achieved in Query Editor, but I'm not sure if it's allowed in direct query mode

Closes 1*("1/"&Datos[Mes_Comercial]&"/"&[Anno Comercial])

Create a relationship from this Date column to the Date column of the calendar table.

Write this measure

ACUM META - CALCULATE([MC_CANT SALES BUDGETED BY CATEGORY];DATESYTD(Calendar[Date],"31/12"))

I hope this helps.

[frenst]

Thanks, the logic you propose worked fine. I construct another time dimension with the date field, then create a relationship with my table, and create a new measure that cummulate fine.

Thanks

[Ashish_Mathur]

You are welcome.