Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
David_C
Regular Visitor

Rolling Sum Calculation

‎02-20-2017 05:37 AM

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-01            A89764

2016-01-15            987643

2016-02-02            B98765

...

...

2016-12-31           F796435

 

And so on. What I'm trying to do is to count the quantity of W/O by month period then summarize that with a 3 month moving window. For example, we will sum the quantity of W/O from 2016-10-01 up to 2016-12-31. Then the next month, the value will go from 2016-11-01 up to 2017-01-31. I have searched the forum and tried lot of formula but nothing resolve my issues. Any ideas and help will be greatly appreciated.

 

Thank you! 

Labels:
Message 1 of 11
5,163 Views
0
2 ACCEPTED SOLUTIONS
Phil_Seamark
Employee
Employee
In response to David_C

‎02-20-2017 08:29 AM

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" )
	)
To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Message 7 of 11
6,950 Views
Phil_Seamark
Employee
Employee
In response to David_C

‎02-20-2017 11:03 AM

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Message 9 of 11
6,897 Views
0
10 REPLIES 10
Sean
Community Champion
Community Champion

‎02-20-2017 06:24 AM

Do you have a Calendar Table?

And

You only want to count full/completed months right?

Message 2 of 11
5,136 Views
0
David_C
Regular Visitor
In response to Sean

‎02-20-2017 06:32 AM

I do have a calendar table however I would prefer to use the date that already exist in my table. And yes I want full/completed months calculation.

Thank you!

Message 3 of 11
5,132 Views
0
Sean
Community Champion
Community Champion
In response to David_C

‎02-20-2017 06:38 AM

1) Create a COLUMN in your Calendar Table

 

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

2) And then the 3 Month RT MEASURE

 

3 Month RT MEASURE = 
CALCULATE (
    COUNTROWS ( Table ),
    DATESINPERIOD (
        Table[Date],
        CALCULATE (
            LASTDATE ( 'Calendar'[Date] ),
            FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Full Month] = "Full Month" )
        ),
        -3,
        MONTH
    )
)

As I posted yesterday DATESINPERIOD works even without a Calendar Table

as you see in formula above the Table[Date] column - the 1st argument is your Table not the Calendar

we only use the Calendar to get the last date of the last full month

 

Hope this helps! Smiley Happy

Message 4 of 11
5,129 Views
David_C
Regular Visitor
In response to Sean

‎02-20-2017 06:48 AM

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!

Message 5 of 11
5,123 Views
0
David_C
Regular Visitor
In response to David_C

‎02-20-2017 07:44 AM

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!

Message 6 of 11
5,114 Views
0
Phil_Seamark
Employee
Employee
In response to David_C

‎02-20-2017 08:29 AM

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" )
	)
To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 7 of 11
6,951 Views
Sean
Community Champion
Community Champion
In response to Phil_Seamark

‎02-20-2017 11:06 AM

Okay I think we got it now! Smiley Happy

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! Smiley Happy

 

 

 

Message 10 of 11
5,077 Views
0
Anonymous
Not applicable
In response to Sean

‎11-20-2018 11:58 AM

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

Message 11 of 11
2,118 Views
0
David_C
Regular Visitor
In response to Phil_Seamark

‎02-20-2017 10:59 AM

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

Have a good day!

Message 8 of 11
5,084 Views
0
Phil_Seamark
Employee
Employee
In response to David_C

‎02-20-2017 11:03 AM

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 9 of 11
6,898 Views
0

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

View All