First value / approval

[KatkaS]

Hello,

could anyone help me please with following:

I have a table where entities are approving a report. Some entities once (MFR), some several times (PHD).

I need to calculate the FIRST REPORTED TIME (if it was late or on time comparing Update time and App deadline, this I'm able to do:)).

Could you please help me how?

Thank you!

[zaza · Accepted solution]

Youn need the first reported time:

First Reported Time = 
    CALCULATE ( 
        MIN ( 'Table'[Update Time] ),
        ALLEXCEPT ( 'Table', 'Table'[Model], 'Table'[Entity], 'Table'[Period] )
    )

[KatkaS]

@zaza Thank you!! This is exactly what I needed.. thank you for solving this despite my blurry explanation, but I really did my best:) 

@parry2k , @az38 , @v-yuta-msft Thank you for you time too!!

[parry2k]

@zaza that would work but only in case if @KatkaS looking for minimum value across all those 3 columns, but if the requirement is first entry then it will not work, and that was the question I asked. 

[v-yuta-msft]

@KatkaS ,

---

I have a table where entities are approving a report. Some entities once (MFR), some several times (PHD).

I need to calculate the FIRST REPORTED TIME (if it was late or on time comparing Update time and App deadline, this I'm able to do:)).

---

Could you please clarify more details about this requirement? For example, you can provide sample data using sheet format and give the expected result.

Regards,

Jimmy Tao

[KatkaS]

Thank you for looking into this!

I've attached an excel sheet with an example below.

I need to calculate if the entities were On time with their finance reporting:

This calculation is easy and I do it as following: If Update time minus App deadline is higher than zero, then it is On time (I use Custom columns to do it). 

Problem is that entities can report several times, like e.g. Entity F below. And I need to take in cionsideration only first approval...

I hope it is clearer now?

Thank you very muhc!

MODEL	ENTITY	UPDATE TIME	APP DEADLINE
FINANCE_INPUT	A	2020-04-03 20:30:33	2020-04-03 21:00
FINANCE_INPUT	B	2020-04-03 19:21:54	2020-04-03 16:00
FINANCE_INPUT	C	2020-04-03 19:01:09	2020-04-03 10:00
FINANCE_INPUT	D	2020-04-03 18:54:11	2020-04-03 10:00
FINANCE_INPUT	E	2020-04-03 17:42:53	2020-04-03 16:00
FINANCE_INPUT	F	2020-04-03 16:01:47	2020-04-03 16:00
FINANCE_INPUT	F	2020-04-03 15:56:24	2020-04-03 16:00

[KatkaS]

update 2:

Hello,

I should probably also mention that in the file, there are different types of reports (column MODEL) and different periods and I need to calculate if the reporting was on time per Entity, Model and per Period..

MODEL	PERIOD	ENTITY	UPDATE TIME	APP DEADLINE
Sales	March2020	A	2020-04-03 20:30:33	2020-04-03 21:00
Sales	March2020	B	2020-04-03 20:05:04	2020-04-03 21:00
Sales	March2020	B	2020-04-03 22:15:04	2020-04-03 21:00
Sales	February2020	B	2020-04-03 19:21:54	2020-04-03 16:00
FINANCE_INPUT	March2020	C	2020-04-03 19:01:09	2020-04-03 10:00
FINANCE_INPUT	March2020	D	2020-04-03 18:54:11	2020-04-03 10:00
FINANCE_INPUT	March2020	E	2020-04-03 17:42:53	2020-04-03 16:00
FINANCE_INPUT	March2020	F	2020-04-03 16:01:47	2020-04-03 16:00
FINANCE_INPUT	March2020	F	2020-04-03 15:56:24	2020-04-03 16:00

[parry2k]

@KatkaS I guess in case of entity F, you want updated time, minimum time from both the records or you want the first record, if you want updated time from first records, what identifies the first record?

[KatkaS]

Hello Parry,

in case of entity F, I only want first record - first record is the one that comes first timewise - in column UPDATE TIME (in case of entity F first is UPDATE TIME at 15:56:24).

I hope it clearer now.. thank you!

[parry2k]

@KatkaS I thought so too and that's why I didn't want to give any solution. So my question was, is there any identifier which record is first, if not then you need to add an index column in power query? Let's start here and then we can get to the solution.

[parry2k]

@zaza agreed but not based on the sample data @KatkaS  provided. 😛

[zaza]

@parry2k maybe I'm just too tired, but isn't the first update time the MIN value? ¯\_(ツ)_/¯

[az38]

@KatkaS 

based on your example what result do you expect?

[KatkaS]

@az38 I have to calculate how many reports were reported on time and how many were late (App deadline minus Update time)...

[az38]

Hi @KatkaS 

it still not completely clear, but you can try s measures like

In Time = CALCULATE(COUNTROWS(Table), Table[Update Time] <= Table[App deadline])

and

Late = CALCULATE(COUNTROWS(Table), Table[Update Time] >= Table[App deadline])