calculate rows measure with time conditions

[blazko]

Hi, what am I doint wrong here?

orders_after_last_action = CALCULATE(
	COUNTROWS('base_data');
	FILTER('base_data';
		'base_data'[location]="loc_A"&&
		'base_data'[operation_start]>MAX('base_data'[operation_end]))
)

And this table, with columns :

 

I want to put this measure in visual table iwth data and user name, next to MIN and MAX time of action (daily working time). So I would have orders done after certain worker last activity this day., in certain location. So user001, for loc_A, woud have 2.

Ok so while writing the topic, i see what the problem is, if i put this in the visual nex to user and date, the calculation will be 0, beacause its counting for only 1 user id. Need to put ALL() statement on user column somewhere in here? 

[v-huizhn-msft]

Hi @blazko,

Actually, I am still confusing your requirement. "So user001, for loc_A, woud have 2" why the result should be 2? Could you please share more details and list your expected result for further analysis?

Best Regards,
Angelia

[blazko]

Hi, thank for response 🙂

I want to count orders that were started after certain user ended his last order. So In this example it would be 2. 2 orders (3 in total, 2 in loc_A) started by user006, after user001 ended his orders.

[v-huizhn-msft]

Hi @blazko,

From the resource table you given above, you want to get a result: user006, 2. How about user 001?

Best Regards,
Angelia

[blazko]

Hi, it can be orders from dozens of other users, i'd like to count. I want to take the max (last) time of finished order , and count all the orders started after that. So User001 ended his last ordeer at 12:12:40 (from above example table), and i need to count these 2  (in this example) orders from 13:01:01 and 13:15:00. As they took place after user001 ended his last order.

So in the end, yellow border as my problem measure:

[v-huizhn-msft]

Hi @blazko,

You'd better post the sample table in table rather than picture in this forum later, so that we can reproduce your scenario easily.

Please create a calculated column using the formula.

MAX=CALCULATE(MAX(Table[operation_end]),ALLEXCEPT(Table,Table[worker],location))

Then create a calcuated column using the formula.

orders=IF(Table[operation_end]=Table[Max],CALCULATE(COUNTA(Location),ALLEXCEPT(Table,Table[Location]))-CALCULATE(COUNTA(worker),ALLEXCEPT(Table,Table[worker])),0)

At last, you can create a visual, filter them to get the expected rows.

Best Regards,
Angelia

[blazko]

Hi, it doesnt really calculate what I want to. Mayby I'll post sample  .pbix, and .xls with example what i want to do.
.xlsx

.pbix