Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
i have a table that has sales aggregated by month. The table has 5 columns:
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!
Solved! Go to 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])
)
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.
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.
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
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])
)
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |