Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Need Urgent Help - Average TIME Calculation differs between Excel and PowerBI

Before i go into the details, here is the sample data that i am working on. this is swipecard data and it contains all the in's and out's of users..   https://1drv.ms/x/s!AhiQ2f7YQHC-gbNYMGJz0l0KU...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    v-danhe-msft thanks for the reply.

     

     

    I tried the below dax and it worked for me.

     

    New table Dax:-

     

    NewTable =
 ADDCOLUMNS(
    SUMMARIZE(
        Table,
        Table[EMP_ID],
        Table[BEGIN_DATE],
        Table[Emp Name]   
    ),

    "InTime", CALCULATE(MIN(Table[Date-Time]),FILTER(ALLEXCEPT(Table,Table[EMP_ID],Table[BEGIN_DATE]),
        Table[Type]="IN")),

    "OutTime",
    CALCULATE(MAX(Table[Date-Time]),FILTER(ALLEXCEPT(Table,Table[EMP_ID],Table[BEGIN_DATE]),
        Table[Type]="OUT"))
               
)

    Then the output of this gives the correct values.

     

     

    Thanks.

    Mohan V

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    v-danhe-msft thanks for the reply.

     

     

    I tried the below dax and it worked for me.

     

    New table Dax:-

     

    NewTable =
 ADDCOLUMNS(
    SUMMARIZE(
        Table,
        Table[EMP_ID],
        Table[BEGIN_DATE],
        Table[Emp Name]   
    ),

    "InTime", CALCULATE(MIN(Table[Date-Time]),FILTER(ALLEXCEPT(Table,Table[EMP_ID],Table[BEGIN_DATE]),
        Table[Type]="IN")),

    "OutTime",
    CALCULATE(MAX(Table[Date-Time]),FILTER(ALLEXCEPT(Table,Table[EMP_ID],Table[BEGIN_DATE]),
        Table[Type]="OUT"))
               
)

    Then the output of this gives the correct values.

     

     

    Thanks.

    Mohan V