Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I need to calculate how many retailers last month whose sales >0 having this month sales =0,
Please help me in this case should I have to create a calculated column or use measure I am stuck on both cases.
I have a simple table which have dates, unique retailers per date and their sales amount but unable to identify retailers from above criteria I am using excel 2016 BI
See attached PBIX.
Thanks Greg for Reply your solution was very helpful but I have much more complex situation here. First find below my data
Date | Sub-Region | Region Code | Sale ID | Agent Type | Retailer ID | PERFORMER_TAG | RET_SEGMENT | Daily Avg | EOD BALANCE | LD USAGE TOTAL | MTD USAGE TOTAL |
8/31/2018 | South 1 | S1-GBT-04 | 7034 | Retailer | 1404392 | PERFORMER | C:Silver | 2 | 10 | 0 | 50 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2102301 | PERFORMER | B:Gold | 696 | 21 | 0 | 21,585 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2176999 | PERFORMER | B:Gold | 1,293 | 7 | 0 | 40,080 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2284888 | PERFORMER | C:Silver | 3 | 6,811 | 0 | 100 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2284999 | PERFORMER | C:Silver | 111 | 3,221 | 100 | 3,450 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2502333 | PERFORMER | C:Silver | 27 | 787 | 0 | 830 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2746273 | PERFORMER | C:Silver | 65 | 22 | 30 | 2,030 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2747497 | PERFORMER | C:Silver | 231 | 2 | 0 | 7,167 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2390514 | PERFORMER | C:Silver | 1 | 97 | 0 | 30 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2390518 | PERFORMER | C:Silver | 15 | 3 | 0 | 460 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2390623 | PERFORMER | C:Silver | 5 | 23 | 0 | 150 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2390863 | PERFORMER | C:Silver | 410 | 11 | 0 | 12,710 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2392834 | PERFORMER | C:Silver | 2 | 2 | 0 | 70 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2393974 | PERFORMER | C:Silver | 65 | 62 | 150 | 2,010 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2394276 | PERFORMER | C:Silver | 69 | 622 | 0 | 2,150 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2395974 | PERFORMER | C:Silver | 1 | 5 | 0 | 30 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2396433 | PERFORMER | C:Silver | 118 | 4 | 0 | 3,665 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2397433 | PERFORMER | B:Gold | 3,071 | 2,774 | 0 | 95,191 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2397745 | PERFORMER | C:Silver | 132 | 6 | 0 | 4,104 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2399006 | PERFORMER | C:Silver | 17 | 0 | 0 | 515 |
8/31/2018 | South 1 | S1-GBT-04 | 7149 | Retailer | 2399199 | PERFORMER | C:Silver | 1,266 | 26 | 0 | 39,260 |
now as you can see I have MTD sales in my data so I don't need month wise filtering I have replaced current month and previous month as dates and I have heirarchy of retailer id , Sales ID , Region Code.
your solution is working fine when run in Card tile only but when I use this in pivot table which have Date External Filter and Region Code & Ret_Segement on row this is not working fine below snapshot
in below mention Pic Total Retailer is Current Date Retailer which is filtered throught external date and mmm measure is created with this code
=VAR __currentDate = MAX('EVC Last Day'[Date]) VAR __lastMonthDate = date(2018,8,31) VAR __tmpTable = SUMMARIZE(ALL('EVC Last Day'),'EVC Last Day'[Retailer ID],"__LastMonth",SUMX(FILTER('EVC Last Day','EVC Last Day'[Date]=__lastMonthDate),'EVC Last Day'[MTD USAGE TOTAL]),"__ThisMonth",SUMX(FILTER('EVC Last Day','EVC Last Day'[Date]=__currentDate),'EVC Last Day'[MTD USAGE TOTAL])) RETURN COUNTROWS(FILTER(__tmpTable,[__ThisMonth]=0 && [__LastMonth]>0))
how to make this more dynamically please help
=VAR __currentDate = MAX('EVC Last Day'[Date]) VAR __lastMonthDate = date(2018,8,31) VAR __tmpTable = SUMMARIZE(ALL('EVC Last Day'),'EVC Last Day'[Retailer ID],"__LastMonth",SUMX(FILTER('EVC Last Day','EVC Last Day'[Date]=__lastMonthDate),'EVC Last Day'[MTD USAGE TOTAL]),"__ThisMonth",SUMX(FILTER('EVC Last Day','EVC Last Day'[Date]=__currentDate),'EVC Last Day'[MTD USAGE TOTAL])) RETURN COUNTROWS(FILTER(__tmpTable,[__ThisMonth]=0 && [__LastMonth]>0))
@marmaghan,
I didn't get same picture as yours when creating measure based on the above sample data. Could you please post expected result based on the above sample data?
Regards,
Lydia
I need above measure should be created in such a way that it can count according to rows filters & date filter is that possible.
I have just provide you few lines for understaning of sample data it is not complete
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
In general though, I imagine that the solution will involve SUMMARIZE where the aggregation columns do a SUMX on FILTERED data for previous month and current month. Then you do a COUNTROWS on the FILTER of this SUMMARIZEd temp table based upon your identified criteria and you're home free.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |