Calculate LM performing Retailers this NON Performing

[marmaghan]

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

[Greg_Deckler]

See attached PBIX.

[marmaghan]

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))

[v-yuezhe-msft]

@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

[marmaghan]

I need above measure should be created in such a way that it can count according to rows filters & date filter is that possible.

[marmaghan]

I have just provide you few lines for understaning of sample data it is not complete 

[Greg_Deckler]

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.