Forum Discussion

JmSahuco's avatar
JmSahuco
Frequent Visitor
5 years ago
Solved

Annual cumulative measures between dates

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 a...
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    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.

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    JmSahuco
    Frequent Visitor
    5 years ago

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