Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

KPI comparing dates different tables

Hi,

 

I want to compare 2 date columns in different tables, which are connected with a production number.

 

I'd like to create a KPI for this. I visualized it in a table already to see if production was finished on the planned date, but I do not know how to create measures / columns to visualize this in a KPI: % of the time production was finished as planned. 

 

Thanks for your suggestions.

Need Help

5 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    I understand what you want, but it is difficult to reproduce your situation without data.

    Could you please share some sample data and your desired result?So we can help you soon.

     

    Best Regards

    Janey Guo

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

       

      So here is some sample data:

      Prod Order Nr.     Planned Date    Actual Date    Timing

      1                           12/11/2020       12/11/2020     On time

      2                           12/11/2020       20/11/2020    Too late

      3                           12/11/2020       05/11/2020    Too early

       

      The timing column is something I added myself. All production orders that are on time or too early are good, but those that are too late are bad (compared to planned date). Now the columns planned and actual date are both in different tables. These are linked with a many to many relationship.

       

      I have thus far tried this:

      Dates in time = 

      If(OR(SUMX('Planned Production','Planned Production'[Planned End date]) > SUMX('Actual Production','Actual Production'[Actual End Date]), 

      (SUMX('Planned Production','Planned Production'[Planned End Date]) = SUMX('Actual Production','Actual Production'[Actual End Date]))),1,0)

       

      It seems to work for most dates, which I can see when I insert this measure into a table and display the dates in time as a column. A 1 appears for those in time / too early and a 0 appears for those that are too late. That way I calculate % production in time as: dates in time / total production orders

       

      However random production orders show a 0 where that should be a 1 and vice versa. Any clue where this goes wrong or if there is another (simpler) solution?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    The issue might be that e.g. the actual date table has e.g. 5 entries for production number 1 while the planned date table has only 1 entry for production number 1. This is due to scanning equipment (start, stop, breaks etc) which is in the actual date table, but not in the planned date table.

     

    I want to compare the planned date with the last actual date of the same production number

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Community Support
      5 years ago

      Hi, Anonymous 

       

      You can try like this:

      a =
IF (
    OR (
        MAXX ( 'Planned Production', 'Planned Production'[Planned End date] )
            > MAXX ( 'Actual Production', 'Actual Production'[Actual End Date] ),
         (
            MAXX ( 'Planned Production', 'Planned Production'[Planned End Date] )
                = MAXX ( 'Actual Production', 'Actual Production'[Actual End Date] )
        )
    ),
    1,
    0
)

      Best Regards

      Janey Guo

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.