Forum Discussion

KatkaS's avatar
KatkaS
Post Patron
6 years ago
Solved

First value / approval

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 REPORT...
Need Help
  • zaza's avatar
    zaza
    6 years ago

    Youn need the first reported time:

     

     

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

     

KatkaS's avatar
KatkaS
Post Patron
6 years ago

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!

MODELENTITYUPDATE TIMEAPP DEADLINE
FINANCE_INPUTA2020-04-03 20:30:332020-04-03 21:00
FINANCE_INPUTB2020-04-03 19:21:542020-04-03 16:00
FINANCE_INPUTC2020-04-03 19:01:092020-04-03 10:00
FINANCE_INPUTD2020-04-03 18:54:112020-04-03 10:00
FINANCE_INPUTE2020-04-03 17:42:532020-04-03 16:00
FINANCE_INPUTF2020-04-03 16:01:472020-04-03 16:00
FINANCE_INPUTF2020-04-03 15:56:242020-04-03 16:00

 

KatkaS's avatar
KatkaS
Post Patron
6 years ago

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..

 

MODELPERIODENTITYUPDATE TIMEAPP DEADLINE
SalesMarch2020A2020-04-03 20:30:332020-04-03 21:00
SalesMarch2020 B2020-04-03 20:05:042020-04-03 21:00
SalesMarch2020B2020-04-03 22:15:042020-04-03 21:00
SalesFebruary2020B2020-04-03 19:21:542020-04-03 16:00
FINANCE_INPUTMarch2020 C2020-04-03 19:01:092020-04-03 10:00
FINANCE_INPUTMarch2020 D2020-04-03 18:54:112020-04-03 10:00
FINANCE_INPUTMarch2020 E2020-04-03 17:42:532020-04-03 16:00
FINANCE_INPUTMarch2020 F2020-04-03 16:01:472020-04-03 16:00
FINANCE_INPUTMarch2020 F2020-04-03 15:56:242020-04-03 16:00
  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      KatkaS
      Post Patron
      6 years ago

      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's avatar
        parry2k
        Super User
        6 years ago

        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.