Count Values with condition

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.

-----------------------------------------------------

LinkedIn

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.

-----------------------------------------------------

LinkedIn

you're THE BEST!!!

Thank you!!!

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.

-----------------------------------------------------

LinkedIn