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 product...
Need Help
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?