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.

David_C's avatar
David_C
Regular Visitor
9 years ago

Ok I will give a try. I have already tested with DatesInperiod function in the past but not exactly as you did. I will let you know.

Thank you!

David_C's avatar
David_C
Regular Visitor
9 years ago

Ok I did test the formula you provided me. However, I still get a monthly value while I want a cumulated value. 

Ex.: September 2016 will hold the value that sum all the WO from a 3 month period , Jully 2016 up to September 2016 and so on up January 2017 that will hold value of November 2016 up to January 2017. Each month must display the sum of last 3 month period. I hope I'm precise enough. Thanks for your help!

  • 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" )
	)
    • David_C's avatar
      David_C
      Regular Visitor
      9 years ago

      Thank you Guys! With all your help and the final solution from Phil it works great, exactly what I need.

      Have a good day!

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

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

    • 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