sumx filter issue

[ptnewman]

i have a table that has sales aggregated by month.  The table has 5 columns:

• month end date
• product name
• beginning sales amount
• ending sales amount
• sales delta amount

  i am trying to show the percent increase of sales for any given range of dates (am using date slider).

for example my table would have the following:

date                product         beginning sales amount   ending sales amount  sales delta amount  

12/31/2016           hat                          9,200                              10,000                               800

12/31/2016          coat                       19,000                              20,000                              1000

1/31/2017             hat                        10,000                              11,000                            1,000

1/31/2017            coat                       20,000                              21,300                            1,300

2/28/2017              hat                       11,000                              12,200                            1,200

2/28/2017            coat                       21,300                              23,500                            2,200

3/31/2017              hat                       12,200                              13,100                               900

3/31/2017            coat                       23,500                              15,000                             1500

user can select 1 or more months, and I want to calculate the % increase in sales by this simple formula

sum(sales delta amount)/sum(beginning sales amount)

if my user selects just the month of March 2017, then the % increase is (900+1500)/(12,200+23,500) = 6.7% here 35,700 is the sum of the beginning amount for the earliest month, jan.

if my user selects jan, feb, march 2017, then % increase is (1000+1300+1200+2200+900+1500)/(10000+20000) = 27%  here 30,000 is the sum of the beginning amount for the earliest month, jan.

so i always have to find the starting sales amount, divide that into the sum of sales for the whole period.

this is what i did:

1) create new measure to get the minimum date selected 

first_month_date = min('sales_agg'[month_end_date])

2) create new measure to sum all the beginning amounts to create the divisor, what i am trying to do is only sum the records that  have a date equal to the beginning month using the filter and sumx

Starting Amount=sumx(filter('sales_agg','sales_agg'[month_end_date]='sales_agg'[first_month_date ]),'sales_agg'[begin_sales_amt])

this sumx(filter()) clause is not working properly, it sums all the records not just those whose date is equal to the first month.  when i display the first month, it is correct, no matter how many months i select, it always is the lowest month of those selected.  just cant figure out why the filter is not working.

any help would be greatly appreciated!

thanks!

[ptnewman]

whoops small typo

if my user selects just the month of March 2017, then the % increase is (900+1500)/(12,200+23,500) = 6.7% here 35,700 is the sum of the beginning amount for the earliest month, jan.

should read

if my user selects just the month of March 2017, then the % increase is (900+1500)/(12,200+23,500) = 6.7% here 35,700 is the sum of the beginning amount for the earliest month, March.

[mattbrice]

How about:

Sales Delta Amt =
CALCULATE (
    SUM ( 'sales_agg'[sales delta amount] ),
    ALLSELECTED ( Calendar[Date] )
)

Sales Beginning =
CALCULATE (
    SUM ( 'sales_agg'[beginning sales amount] ),
    ALLSELECTED ( Calendar[Date] )
)


% increase = DIVIDE ( [Sales Delta Amt] , [Sales Beginning] )

 which assumes you are using a separate best practice calendar table...if not you may be able to switch out 'Calendar[Date]' with 'sales_agg'[month end date]  inside the ALLSELECTED() call.

[ptnewman]

Matt, thanks for chiming in.  

I need the sales beginning to only sum the earliest's months "beginning sales amount".  this sum would be the total sales at the beginning of the period.  if my user selects 3 months, i believe your calculation would sum the begininng amounts for all 3 months, which will not work.  I will look at the calculate / sum / allselected to see if there is something i can use there.  

thanks, Paul

[ptnewman · Accepted solution]

Eventually tracked down an excel jockey and he provided the answer.  really simple ...

Sales Beginning = CALCULATE (

     SUM ( 'sales_agg'[beginning sales amount] ),

     FIRSTDATE('sales_agg'[begin_sales_amt])

)