KPI comparing dates different tables

[Anonymous]

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.

[Anonymous]

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]

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.

[v-janeyg-msft]

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]

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?

[amitchandak]

@Anonymous , refer to my blog on these topics

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451