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

 

 

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

     

14 Replies

  • zaza's avatar
    zaza
    Resolver III
    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] )
    )

     

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      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. 

    • KatkaS's avatar
      KatkaS
      Post Patron
      6 years ago

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

    • KatkaS's avatar
      KatkaS
      Post Patron
      6 years ago

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

      • az38's avatar
        az38
        Community Champion
        6 years ago

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

         

  • 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