Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, I need help please!
I have a table
SaleRepName - ItemNum – SaleDate - SaleTime
I need to count the number of sales per SaleRep, BUT
all the sales made by the same SaleRep within 2.5 minutes period must be counted as 1
Ex.:
Alex 12345 2022-11-20 15:15:01
Alex 12345 2022-11-20 15:17:01
Alex 12345 2022-11-20 15:25:01
Max 54321 2022-11-20 15:30:05
Max 54321 2022-11-20 15:32:05
BOB 12555 20222-11-21 16:21:05
Expected result: Alex – 2 Sales, Max – 1 Sale, Bob – 1Sale
Thank you,
Solved! Go to Solution.
@Kukusiki83 , Try a new column like
Var _datetime =[SaleDate] - [SaleTime]
var _max = maxx(filter(Table, Table[SalesRepName] = earlier([SalesRepName]) && ([SaleDate] - [SaleTime]) < [SaleDate] - [SaleTime]) , [SaleDate] - [SaleTime])
return
if( datediff(_max,_datetime ,second) <150, 0,1)
and you can sum this column
Hi @Kukusiki83
Please try the following approach using a measure.
Sales Made by Sales Rep =
SUMX(
VALUES('Sample'[Sales Rep]),
CALCULATE(
SUMX(
VALUES('Sample'[Date-Time]),
var var_CurrentTime = [Date-Time]
var var_TimeBefore = CALCULATE(MAX('Sample'[Date-Time]),'Sample'[Date-Time] < var_CurrentTime) + 0
RETURN
IF( DATEDIFF(var_TimeBefore,var_CurrentTime,SECOND) <150, 0,1)
)
)
)
Please before you start creating the Measure make sure that you combine the date and the time column. I did it upfront in Power Query like this. There might be easier ways but I focussed on solving the emasure problem. 🙂
Get combined Date-Time:
Base
Transform columns to decimal:
Combine columns with custom function
Change type of new column to date time
Remove date and time column
Base on this table please use the DAX measure on in the beginning of the post
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi @Kukusiki83
Can you please try:
Sales Made by Sales Rep =
SUMX(
SUMMARIZE(
'Sample',
'Sample'[Sales Rep],
'Sample'[Sales Item]
),
CALCULATE(
SUMX(
VALUES('Sample'[Date-Time]),
var var_CurrentTime = [Date-Time]
var var_TimeBefore = CALCULATE(MAX('Sample'[Date-Time]),'Sample'[Date-Time] < var_CurrentTime) + 0
RETURN
IF( DATEDIFF(var_TimeBefore,var_CurrentTime,SECOND) <150, 0,1)
)
)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi @Kukusiki83
I might have a solution. If you have performance issues somteimes its better to use a calculated column. So luckkily I still saved the sample data and measure for your case.
Can you please try the following approach?
1) Create in your table the followin calculated column:
Calculated Column - Sales COunter =
var var_CurrentSalesRep = [Sales Rep]
var var_CurrentSalesTime = [Date-Time]
var var_SalesTimeTreshHold = [Date-Time] - TIME(0,0,150)
var var_Sales_Previos_150_Secs =
COUNTROWS(
FILTER(
'Sample',
[Sales Rep] = var_CurrentSalesRep &&
[Date-Time] < var_CurrentSalesTime &&
[Date-Time] >= var_SalesTimeTreshHold
)
)
RETURN
IF(var_Sales_Previos_150_Secs>0,0,1)
Put the column in your matrix (with sum) or create a sum emasure on top (the yellow one is the new calculated column):
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi @Kukusiki83
Please try the following approach using a measure.
Sales Made by Sales Rep =
SUMX(
VALUES('Sample'[Sales Rep]),
CALCULATE(
SUMX(
VALUES('Sample'[Date-Time]),
var var_CurrentTime = [Date-Time]
var var_TimeBefore = CALCULATE(MAX('Sample'[Date-Time]),'Sample'[Date-Time] < var_CurrentTime) + 0
RETURN
IF( DATEDIFF(var_TimeBefore,var_CurrentTime,SECOND) <150, 0,1)
)
)
)
Please before you start creating the Measure make sure that you combine the date and the time column. I did it upfront in Power Query like this. There might be easier ways but I focussed on solving the emasure problem. 🙂
Get combined Date-Time:
Base
Transform columns to decimal:
Combine columns with custom function
Change type of new column to date time
Remove date and time column
Base on this table please use the DAX measure on in the beginning of the post
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi. it worrks perfect. thank you so much.
I just was wondering, how can I add onother condition on top of it:
all the sales made by the same SaleRep within 2.5 minutes period must be counted as 1, only if the item number is the same.
Thank you!!!
Hi @Kukusiki83
Can you please try:
Sales Made by Sales Rep =
SUMX(
SUMMARIZE(
'Sample',
'Sample'[Sales Rep],
'Sample'[Sales Item]
),
CALCULATE(
SUMX(
VALUES('Sample'[Date-Time]),
var var_CurrentTime = [Date-Time]
var var_TimeBefore = CALCULATE(MAX('Sample'[Date-Time]),'Sample'[Date-Time] < var_CurrentTime) + 0
RETURN
IF( DATEDIFF(var_TimeBefore,var_CurrentTime,SECOND) <150, 0,1)
)
)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi. I hope u'll be able to help me again.
The DAX that u gave me works perfectly with a small sample of data. when I try to make it work with my real data (40k+ lines) it just freezes...
Is there a way to simplify it somehow?
My data is in excel, so would it help to make the calculation directly in excel and uploaod it to PowerBI? If so, how?
Thanks
Hi @Kukusiki83
I might have a solution. If you have performance issues somteimes its better to use a calculated column. So luckkily I still saved the sample data and measure for your case.
Can you please try the following approach?
1) Create in your table the followin calculated column:
Calculated Column - Sales COunter =
var var_CurrentSalesRep = [Sales Rep]
var var_CurrentSalesTime = [Date-Time]
var var_SalesTimeTreshHold = [Date-Time] - TIME(0,0,150)
var var_Sales_Previos_150_Secs =
COUNTROWS(
FILTER(
'Sample',
[Sales Rep] = var_CurrentSalesRep &&
[Date-Time] < var_CurrentSalesTime &&
[Date-Time] >= var_SalesTimeTreshHold
)
)
RETURN
IF(var_Sales_Previos_150_Secs>0,0,1)
Put the column in your matrix (with sum) or create a sum emasure on top (the yellow one is the new calculated column):
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
thanks again, it worked better with a bigger set of data.
@Kukusiki83 small disclaimer to the new approach. Since we use a calculated column it could be that the reoprt refresh takes a little bit longer. But in the report view and when the user interacts with the report it should be much fast because the core claculation is done upfront.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
you're THE BEST!!!
Thank you!!!
Thank you for your feedback! 🙂
@Kukusiki83 , Try a new column like
Var _datetime =[SaleDate] - [SaleTime]
var _max = maxx(filter(Table, Table[SalesRepName] = earlier([SalesRepName]) && ([SaleDate] - [SaleTime]) < [SaleDate] - [SaleTime]) , [SaleDate] - [SaleTime])
return
if( datediff(_max,_datetime ,second) <150, 0,1)
and you can sum this column
it doesn't seem to work (unless I'm doing something wrong) I get a column with 0 only
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |