Forum Discussion

ffarah's avatar
ffarah
Regular Visitor
2 years ago
Solved

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

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:

CountryRegion IDnov/22dez/22jan/23fev/23mar/23
A23000283  110108106
A23001011   9587
A23002115 370359 379
A23002468 299286260255
A23004088   203202
A23004258 510494496474
A23005033 260246242237

 

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

CountryRegion IDnov/22dez/22jan/23fev/23mar/23
A23000283   -1,8%-1,9%
A23001011    -8,4%
A23002115  -3,0%  
A23002468  -4,3%-9,1%-1,9%
A23004088    -0,5%
A23004258  -3,1%0,4%-4,4%
A23005033  -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:

Countrynov/22dez/22jan/23fev/23mar/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!

@previous month
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    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.

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      ffarah
      Regular Visitor
      2 years ago

      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!

  • ffarah's avatar
    ffarah
    Regular Visitor
    2 years ago

    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's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      2 years ago

      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.