How do I SUM previous month data only if there is both current data and previous month data

[ffarah]

Hi, everyone! 

I'm new to the Power Tools and trying to calculate growth month over month of comparable Regions (kind of like Same Store Sales month over month) but I'm having a lot of trouble. I am working on Power Query but also testing in Power BI to see if there is a difference. 

So this is a sample data:

Country	Region ID	nov/22	dez/22	jan/23	fev/23	mar/23
A	23000283			110	108	106
A	23001011				95	87
A	23002115		370	359		379
A	23002468		299	286	260	255
A	23004088				203	202
A	23004258		510	494	496	474
A	23005033		260	246	242	237

What I want to do is have the following growth rates :

Country	Region ID	nov/22	dez/22	jan/23	fev/23	mar/23
A	23000283				-1,8%	-1,9%
A	23001011					-8,4%
A	23002115			-3,0%
A	23002468			-4,3%	-9,1%	-1,9%
A	23004088					-0,5%
A	23004258			-3,1%	0,4%	-4,4%
A	23005033			-5,4%	-1,6%	-2,1%

where

Growth rate = (sales)/(previous month sales)-1

but only if there are both month sales and previous month sales. 

And a Total for the country would be:

Country	nov/22	dez/22	jan/23	fev/23	mar/23
A			-3,8%	-2,6%	-3,1%

Only adding Regions where there where sales in both the Month and Previous Month, so the growht in Jan would be from regions 

23002115

23002468

23004258

23005033

 and not include region 23000283.

This link has an Excel file with formulas, a PBI file with the data and the csv files.

https://we.tl/t-QDlhXnKn3N

thanks in advance!

[ffarah]

I also thought of adding a column with the previous month's value but was not able to get the value I wanted.

[v-zhangti · Accepted solution]

Hi, @ffarah 

You can try the following methods.

Previous value = 
Var _previousdate=CALCULATE(MAX(Sales[Month]),FILTER(ALLEXCEPT(Sales,Sales[Country],Sales[Region ID]),[Month]<SELECTEDVALUE(Sales[Month])))
Return
CALCULATE(SUM(Sales[Value]),FILTER(ALLEXCEPT(Sales,Sales[Country],Sales[Region ID]),[Month]=_previousdate))

Growth rate = DIVIDE(SUM(Sales[Value])-[Previous value],[Previous value])

Measure = 
Var _mindate=CALCULATE(MIN(Sales[Month]),ALL(Sales))
Var _mindatevalue=CALCULATE(SUM(Sales[Value]),FILTER(ALLEXCEPT(Sales,Sales[Region ID],Regions[Country]),[Month]=_mindate))
RETURN
IF(_mindatevalue=BLANK(),0,[Previous value])

Measure 2 = 
Var _table=FILTER(SUMMARIZE(Sales,Sales[Country],Regions[Region ID],Sales[Month],Sales[Value],"Previous value",[Measure]),[Previous value]<>0)
Var _Sum1=SUMX(_table,[Value])
Var _Sum2=SUMX(_table,[Previous value])
Return
DIVIDE(_Sum1-_Sum2,_Sum2)

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @ffarah ,

Time Intelligence could be handy in cases like yours. Try to:

1) create a calculated table like

dates = 
ADDCOLUMNS(
    CALENDAR(MIN(Sales[Month]), MAX(Sales[Month])),
    "YY/MM", FORMAT([Date], "YY/MM")
)

2) related the dates[date] column with sales[month] column

3) plot a Matrix visual with dates[YY/MM], regions[regions ID] and a measure like:

MoM% = 
VAR _sales = SUM(Sales[Value])
VAR _salespre =
CALCULATE(
    SUM(Sales[Value]),
    PREVIOUSMONTH(dates[Date])
)
VAR _diff = DIVIDE(_sales, _salespre) -1
VAR _result = 
IF(
    _salespre=BLANK()||_sales=BLANK(), 
    "", _diff
)
RETURN _result

it worked like:

[ffarah]

Hi, Freemanz

Thank you very much for the help! This works nicely for each Region MoM but the country total doesn't match. It is still summing all the regions and then dividing. 

I think the condition has to be in the sum momment. Kind of like:

VAR _sales = SUM(if(Sales[Value]=blank(),0,Sales[Value]))

OR

VAR _sales = SUM(if(ISBLANK(Sales[Value]),0,Sales[Value]))

but these sintax don't work.

Thanks for the help!