Annual cumulative measures between dates

[JmSahuco]

Hello everyone

I have a problem when making a cumulative sum, I explain, I have a table with a quantity and two fields date, from and to, I want to make the cumulative sum of the amount (which is always fixed) considering that the date up may be less, I have set as end date 31/12/2050 those that have not yet ended.

the table is more or less like this

id cant start date end date

1 5 01/01/2018 30/06/2019

2 10 05/05/2018 31/12/2050

3 100 01/05/2019 31/12/2050

4 25 10/06/2019 01/01/2020

5 50 10/05/2020 31/12/2050

And I'm looking for an annual cumulative result of the total so

2018 15 (id 1+2)

2019 135 (id 1+2+3+4 - 1)

2020 160 (id 1+2+3+4+5 - 1 -4)

Thanks a lot

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[AllisonKennedy · Accepted solution]

@JmSahuco Do you have a DimDate table? Is it unrelated to the table with Cant, ID, in it?

You can try something like: 

Total Measure =

VAR _MinDate = MIN(DimDate[Date])

VAR _MaxDate = MAX(DimDate[Date])

RETURN

SUMX(FILTER(Table), Table[StartDate] <= _MaxDate && Table[EndDate] >= _MinDate), Table[Cant])

Then put DimDate values in the visualization along with the measure above.

[JmSahuco]

Me ha servido mucho la solución, sólo he tenido que realizar un ligero cambio

funcion = var fmin=min('2-Calendario'[Date])

var fmax=max('2-Calendario'[Date])

return calculate(

sum( table[cant]), FILTER( ALL(table),table[Fecha_Inicio]<=fmax&&table[Fecha_Fin]>=fmin))