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] )
    )

     

v-yuta-msft's avatar
v-yuta-msft
Community Support
6 years ago

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'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?