Analysing variation of status through time from different tables - Line chart

[excelsa]

Hello, I am having a brain freeze issue because I cannot think of a solution to the below - I am sure you guys will come up with something that will be obvious once it's explained to me lol.

So basically I have different sources of data, all containing the same format (i.e. column names) but each source is an updated version which contain the new status of the item. For example:

Table1

Item Name	Status	Date
Item0	Not Verified	01/07/2020
Item1	Not Verified	01/07/2020
Item2	Not Verified	01/07/2020
Item3	Not Verified	01/07/2020
Item4	Not Verified	01/07/2020

Table2

Item Name	Status	Date
Item0	Verified	01/08/2020
Item1	Not Verified	01/08/2020
Item2	Verified	01/08/2020
Item3	Not Verified	01/08/2020
Item4	Not Verified	01/08/2020

Table3

Item Name	Status	Date
Item0	Verified	01/09/2020
Item1	Verified	01/09/2020
Item2	Verified	01/09/2020
Item3	Verified	01/09/2020
Item4	Verified	01/09/2020

I need to somehow get this data together (preferably using data model relationships) and come up with a line chart that would show the number of items per date per status. A bit like the example below:

If you could explain how to join/relate the data, which fields for the chart (do we need to create helper fields?), etc... I would be very very grateful.

 

[offthelip]

I think you just need a row of countif statements like this:

=COUNTIF(Table1[[#All],[Column1]],"Verified")

=COUNTIF(Table2[[#All],[Column1]],"Verified")

=COUNTIF(Table3[[#All],[Column1]],"Verified")

 

[excelsa]

I think you just need a row of countif statements like this:

=COUNTIF(Table1[[#All],[Column1]],"Verified")

=COUNTIF(Table2[[#All],[Column1]],"Verified")

=COUNTIF(Table3[[#All],[Column1]],"Verified")

Hi @offthelip thanks for the reply but I don't think that is the solution I was looking for - although your solution could work for fixed data. I need this to be dynamic so every time a new table (with new status) is added I wouldn't need to update to the formula. I was looking for something using data relationship: is it best to join or can I use relationships between the tables; should the columns be pivoted (which ones); etc.

 

[excelsa]

Hi all, just wanted to mark this post as finished. I think the only way of achieving what I want is by 'appending' the files - which can be achieved by using Power Query for example.

Sorry moderators for not finding the way of closing this thread (I honestly can't find how to close it myself)