Active dealer count

[SachinNamdeo-20]

Dear community,
" I want to calculate active dealers count but don't know how satisfied with this condition active dealers are-

Active dealer:- Who was appear in more than 3 months in a year is the active dealer. it means the dealer has a sale for more than 3 months in a year no matter in one month how many times it's a sale. it has sale more than 3 months in a year c
I have this type of data  and also many column like year, month no etc." 
table name - cube invoice 

date	month	dealer	sales
1-1-22	jan	a,b,c	10,20,30
1-2-22	feb	a,b c,d,	10,20,30,40,
1-3-22	march	a,v,b,c,d,e	10,20,14,02,12
1-4-22	apr	d,e,e,a	10,20,30,82
1-5-22	may
1-6-22	june
1-7-22	july
1-8-22	aug
1-9-22	sep

as we see "a" is apper more than 3 months in year so it is a active dealer so there is active dealer count is 1 
now i want this on dax measure 
please help

[v-yueyunzh-msft]

Hi , @SachinNamdeo-20 

According to your description, you want to calculate the count of active users who have more than 3 sales in different month.

For your table , it not a good data structure to dispose the data.

Here are the steps you can refer to :
(1)This is my test data:

(2)We need to convert the table in Power Query Editor, You can put the M code in "Advanced Editor" to refer to :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3LCYBADATQXnIeMIl7sJfFQ/whgiIerMdarMy4oisJmcsbEiNJIYWyKoEmW87D09Cg9RSGMkqmGpE0u6FvXoXu5xAeWmY629aOCe8PR/8VJIAVcm+qhVyzdfPr2Mf+Hyqn9QU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, month = _t, dealer = _t, sales = _t]),
    Custom1 = Table.TransformColumns(Source,{{"dealer",(x)=>Text.Split(x,",")},{"sales",(x)=>Text.Split(x,",")}}),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Table.FromColumns({[dealer],[sales]},{"dealer","sales"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"dealer", "sales"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"dealer", "sales"}, {"dealer", "sales"})
in
    #"Expanded Custom"

The result table is like this:

(3)Then we can apply the data to Desktop and then we can click "New column" to  add a [Year_month] column in our table as  a dimension:

Year_month = year([Date]) * 100 + MONTH([Date])

(4)Then we can create a measure like this:

Count = var _t = ALLSELECTED('Table')
var _t2 =SUMMARIZE(_t ,[Year_month],[dealer]) 
var _t3 =FILTER(ADDCOLUMNS(_t2 ,"count" ,var _dealer = [dealer] return COUNTROWS(FILTER(_t2 ,[dealer]=_dealer))) , [count]>3)
return
COUNTROWS(DISTINCT(SELECTCOLUMNS(_t3, "dealer" ,[dealer])))

Then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SachinNamdeo-20 · Accepted solution]

Thank you sir for your  all valuable suggestions this is done by this measure

Active Customers = 
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE ( CUBE_INVOICE, CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE]),
        "@num months", COUNTROWS ( CALCULATETABLE ( SUMMARIZE ( CUBE_INVOICE, Calendar_new[YM] ) ) )
    )
RETURN COUNTROWS ( FILTER ( SummaryTable, [@num months] >= 3 ) )

This measure working when we selecct more than 3 month on calender slicer but with this measure we done it for ficial year 

Active dealer in ficial year = CALCULATE([Active Customers],DATESYTD(Calendar_new[Date],"03/31"))

[SachinNamdeo-20]

"I also have this type of data you describe in m codes but your measure gives me blank value plese guide me now what to do This is my datatable ss here ship to partner code is my dealer and have their sales are also combine in next frame please help." 

[v-yueyunzh-msft]

Hi , @SachinNamdeo-20 

Thanks for your quick response!Do you mean you have had the data structure like this:

If this , you need to add a calculated column in this table:

Year_month = year([D DATE.FULLDATE]) * 100 + MONTH([D DATE.FULLDATE])

After it , then we create this measure:

Count = var _t = ALLSELECTED('Table')
var _t2 =SUMMARIZE(_t ,[Year_month],[SHIP TO PARTNER.PARTNER CODE]) 
var _t3 =FILTER(ADDCOLUMNS(_t2 ,"count" ,var _code= [SHIP TO PARTNER.PARTNER CODE] return COUNTROWS(FILTER(_t2 ,[SHIP TO PARTNER.PARTNER CODE]=_code))) , [count]>3)
return
COUNTROWS(DISTINCT(SELECTCOLUMNS(_t3, "SHIP TO PARTNER.PARTNER CODE" ,[SHIP TO PARTNER.PARTNER CODE])))

If this still dose not work , can you share the same data structure sample data like yours to us (without sensitive data) and the end result value you want to ?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SachinNamdeo-20]

" i already used this measure  here it is this is my pbi file please help me its urgent  in this file sold to partner code is my dealer and invoice quantity is their sales and remaining thig is" previous.
Sir ,if you can please response quickly its really urgent

[v-yueyunzh-msft]

Hi , @SachinNamdeo-20 

Thanks for your quick response!

I check the .pbix file, sorry i use the ALLSELCETED() function, so it will be filtered by the slicer.

You cana try to use :

Count = var _t = ALL('CUBE_INVOICE')
var _t2 =SUMMARIZE(_t ,[Year_month],[SHIP TO PARTNER.PARTNER CODE]) 
var _t3 =FILTER(ADDCOLUMNS(_t2 ,"count" ,var _code= [SHIP TO PARTNER.PARTNER CODE] return COUNTROWS(FILTER(_t2 ,[SHIP TO PARTNER.PARTNER CODE]=_code))) , [count]>3)
return
COUNTROWS(DISTINCT(SELECTCOLUMNS(_t3 ,"code", [SHIP TO PARTNER.PARTNER CODE])))

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SachinNamdeo-20]

sir it can not fullfill my condition can we chat one to one this is my mail id 
sachin.namdev@prismjohnson.in

[v-yueyunzh-msft]

Hi, @SachinNamdeo-20 

I'm sorry, I may not fully understand your needs,MY UNDERSTANDING IS TO GROUP BY YEAR AND MONTH [SHIP TO PARTNER. PARTNER CODE], FILTER FOR [SHIP TO PARTNER. PARTNER CODE], and then return distinct count of [SHIP TO PARTNER. PARTNER CODE].

Also, for policy reasons, I can't have other one-on-one conversations with you.

For your question, can you describe your needs in detail and provide your expected outcome values? To make the question clearer and simpler?

If your need is urgent and you need one-on-one communication,

If you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.

It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: https://powerbi.microsoft.com/en-us/support/

For how to create a support ticket, please refer to https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SachinNamdeo-20]

Yes i make my question clear  i was send my pbi file in previous waht is my actual condition i made it clear in my excel sheet whcich i send you now  for example
I pulled out data from pbi file which are sectionally devide into column respectively 
cloumns are dealer,those 12 no are month no and their sales dealerwise 
i want to calculate which dealer is active in finanacial year that is from april it means count all those dealer who has more than 3 time sales in a ficial year are active dealer as in excel sheet i count this from (= count(from apr to ongoing month)
if now you understand my conditon then please help.
i used this measure for calculating my data this give me return value  from APR TO OCT 3192 which has some error my expected value is 2879 from apr to oct which is my actual value 
please help

#Active dealer =

VAR _users =

CALCULATETABLE(

    GROUPBY(

        CUBE_INVOICE,

        CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE],

        "CaseCount", COUNTX(CURRENTGROUP(),CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE])

    ),

   DATESYTD(Calendar_new[Date],"31/03"))

RETURN COUNTROWS( FILTER( _users, [CaseCount] >= 3 ) )

i 

[v-yueyunzh-msft]

Hi, @SachinNamdeo-20 

Thanks for your quick response !

According to your description, you want to count the distint count of [SOLD TO PARTNER.PARTNER CODE] in the "finanacial year(this year April to the next March Month?)".

But in your .pbix file you provide , in the [D DATE.FULLDATE] column , the date is from 2016-2022 . And which year range or the total year you need to calculate?

If you just want to calcualte the max date finanacial year, you can try to use this dax , but i do not find the way to get the "2879 or 3192 " you said.

Count = 
var _max_date_month =month( MAXX(ALL('CUBE_TARGET') , [D DATE.FULLDATE]))
var _max_date_year =year( MAXX(ALL('CUBE_TARGET') , [D DATE.FULLDATE]))
var _from_date = if(_max_date_month>3 , Date(_max_date_year,4,1),   Date(_max_date_year-1,4,1)   )
var _to_date = IF(_max_date_month<=3 ,  Date(_max_date_year,3,31),  Date(_max_date_year+1,3,31))
var _t =FILTER( ALL('CUBE_INVOICE') ,[D DATE.FULLDATE] >= _from_date && [D DATE.FULLDATE] <= _to_date)
var _t2 =SUMMARIZE(_t ,[Year_month],[SHIP TO PARTNER.PARTNER CODE]) 
var _t3 =FILTER(ADDCOLUMNS(_t2 ,"count" ,var _code= [SHIP TO PARTNER.PARTNER CODE] return COUNTROWS(FILTER(_t2 ,[SHIP TO PARTNER.PARTNER CODE]=_code))) , [count]>3)
return
COUNTROWS(DISTINCT(SELECTCOLUMNS(_t3 ,"code", [SHIP TO PARTNER.PARTNER CODE])))

For this i calculate the distinct count of  [SOLD TO PARTNER.PARTNER CODE] in "2022/4/1" to "2023/3/31" :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@SachinNamdeo-20 , I am hoping the dealer and sales are not comma separated. they are in rows.  Use a date  table, but for distinct count use month year from sales table

You can create a measures like

YTD = CALCULATE(Distinctcount(sales[Month Year]) ,DATESYTD('Date'[Date],"12/31"))

GT 3= countx(filter(Values(Sales[Dealer]) , [YTD] >3), [Dealer])

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[SachinNamdeo-20]

"Sir actually they are they are comma seprated it means dealer a has sales 10 and dealr b with 20 sales and so on ,and one thing also sales is a column of cube_invioce so i can not use it like your measure with month year .
these measure gives me value but not not accurate please help sir"