Forum Discussion

David_C's avatar
David_C
Regular Visitor
9 years ago
Solved

Rolling Sum Calculation

Hello to all,   I'm stuck with a simple task from a while now. I have a table that contains fields as follow:   Date:                     W/O id:             2016-01-01            F7895 2016-01...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    I think this might be close to what you need?

     

    3 Month RT MEASURE = CALCULATE (
    COUNTROWS ( 
    	DATESINPERIOD (
        	'Table'[Date],
			LASTDATE('Calendar'[Date]),-3,MONTH)
			),
			FILTER (  'Calendar', 'Calendar'[Full Month] = "Full Month" )
	)
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Sean did the hard yards.  I just tweaked it for the cumulative requirement.

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

I think this might be close to what you need?

 

3 Month RT MEASURE = CALCULATE (
    COUNTROWS ( 
    	DATESINPERIOD (
        	'Table'[Date],
			LASTDATE('Calendar'[Date]),-3,MONTH)
			),
			FILTER (  'Calendar', 'Calendar'[Full Month] = "Full Month" )
	)
Sean's avatar
Sean
Community Champion
9 years ago

Okay I think we got it now! :smileyhappy:

1) Create a COLUMN in your Calendar Table

 

Full Month COLUMN =
IF (
    TODAY () >= EOMONTH ( 'Calendar'[Date], 0 ),
    "Full Month",
    "Incomplete Month"
)

2) And then this NEW MEASURE

 

3 Completed Months Running Total MEASURE =
VAR LastCompleteMonthDate =
    CALCULATE (
        LASTDATE ( 'Calendar'[Date] ),
        FILTER ( 'Calendar', 'Calendar'[Full Month] = "Full Month" )
    )
VAR FirstDate3MonthsPrior =
    CALCULATE (
        FIRSTDATE ( 'Calendar'[Date] ),
        DATESINPERIOD ( 'Calendar'[Date], LastCompleteMonthDate, -3, MONTH )
    )
RETURN
    CALCULATE (
        [YOUR MEASURE],
        DATESBETWEEN ( 'Calendar'[Date], FirstDate3MonthsPrior, LastCompleteMonthDate )
    )

Obviously I deliberately used long descriptive Variable names which you can change

but now this can be easily adjusted by only changing the number in red!

 

Good Luck! :smileyhappy:

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Sean,

    Thanks for that information, it´s works !!!

    Maybe you can help me with a variation, first i am explain what i doing.

    1) with your formula i create those measure

    1.-sumarize of cost

     

    suma_costo_neto_12_meses_movil = 
VAR LastCompleteMonthDate =
    CALCULATE (
        LASTDATE ( 'Date'[Fecha] );
        FILTER ( date; 'Date'[ultimo_mes_completo] = "Full Month" )
    )
VAR FirstDate3MonthsPrior =
    CALCULATE (
        FIRSTDATE ( 'Date'[Fecha] );
        DATESINPERIOD ( 'Date'[Fecha]; LastCompleteMonthDate; -12 ;MONTH)
    )
RETURN
    CALCULATE (
        SUM('Table1'[COSTO]);
        DATESBETWEEN ( 'Date'[Fecha]; FirstDate3MonthsPrior; LastCompleteMonthDate )
    )

    2.- average of sales

     

     

    prom_Colocaciones_12_meses_movil = 
VAR LastCompleteMonthDate =
    CALCULATE (
        LASTDATE ( 'Date'[Fecha] );
        FILTER ( date; 'Date'[ultimo_mes_completo] = "Full Month" )
    )
VAR FirstDate3MonthsPrior =
    CALCULATE (
        FIRSTDATE ( 'Date'[Fecha] );
        DATESINPERIOD ( 'Date'[Fecha]; LastCompleteMonthDate; -12 ;MONTH)
    )
RETURN
    CALCULATE (
        divide(SUM('Table1'[Venta]);12);
        DATESBETWEEN ( 'Date'[Fecha]; FirstDate3MonthsPrior; LastCompleteMonthDate )
    )

    3.- resultado (this is the division of the previous ones)

     

     

    resultado = [suma_costo_neto_12_meses_movil]     /     [prom_Colocaciones_12_meses_movil]

    so if i dont use the dimension date the resultado is perfect but when i use this dimension (because i want to see the evolution of this index) the resultado change for 

     

     

    resultado = cost of this month / ( sales of this moth / 12)

     

    the result when i want is a division of rolling 12 month [sumarize cost / (sumarize sales/12) ] for each month

     

    In green i put the result with your formula with out date dimension

    In blue the result i want for the other month

    In red the result with the dimension date

     

    DateCostoSalesresultado obtenidoresultado deseado
    01/08/201714.316.069.700,0013.320.449.570.495,00  
    01/09/201716.939.617.793,0013.394.740.922.262,00  
    01/10/201714.680.033.193,0013.458.750.519.748,00  
    01/11/20178.871.789.874,0013.605.301.094.424,000,78% 
    01/12/201713.122.665.980,0013.663.005.198.515,001,15% 
    01/01/201816.807.983.599,0013.720.491.064.445,001,47% 
    01/02/201817.209.388.491,0013.749.363.547.223,001,50% 
    01/03/201816.278.829.972,0013.887.556.595.776,001,41% 
    01/04/201814.253.829.989,0013.980.895.401.675,001,22% 
    01/05/201813.394.442.739,0014.072.419.517.952,001,14% 
    01/06/201814.763.259.834,0014.206.538.166.274,001,25% 
    01/07/201815.806.333.890,0014.299.342.357.065,001,33%1,2804%
    01/08/201842.876.304.751,0014.513.141.349.238,003,55%1,4771%
    01/09/201820.094.809.245,0014.617.464.241.314,001,65%1,4889%
    01/10/201815.389.926.467,0014.835.472.808.712,001,24%1,4818%

     

    I hope you can help me

     

    thanks of before hand