Compare 2 tables - Confirm is present, not present or unexpected

[Anonymous]

Hi 

I am trying compare 2 tables with dates to confirm if we have recieved an item

• Table 1 schedule to expected dates
• Table 2 received items

I have tried to minus the 2 related tables as follows:

Status = 

SUMX('Reporting Schedule', 
(DISTINCTCOUNT('Reporting Schedule'[Submission Period +1]))

-
 (DISTINCTCOUNT(_A_Combined[spm_reporting_period]))

)

The expectation (or hope) is i would receive

• -1 = Not expected but received
• 0 = Expected and recieved
• 1 = Expected and NOT received.

[PC2790]

Hi @Anonymous ,

Had a look at your data model and it looks like there are lot of things which needs to be corrected. Few of them are:

1) The Received table does not contain the exact date, so what will be indicator if the product has been received?

2) The relationship between the Expected and Received table should be on the basis of contract and not the dates. Currently it is between Reporting period which is of text data type and Date having DateTime datatype which doesn't make sense at all.

So in conclusion, you will have to look for a unique column in both the tables based on which the relationship can be formed to carry out further operations.

And the datatypes of the columns needs to be corrected. Also, look at the required information of receiving date so that you can get the desired result.

[Anonymous]

hi @PC2790 

Thanks for the reponse

I have updated the model slightly, as it wasnt truly reflecting which is available on the file and the links.

to try and articulate what i am trying to acheive.

We have a schedule for each contract, as present the team manually populate an excel sheet with the date the submissions is received

This table is then unpivoted in powerbi to leave 2 columns, removing all those that have no content

• Reporting period (the column header)
• submission date (the cell values)

Within the dynamics database. the data will contain A line for each metric and Type of contract, contract number and Reporting Period

They are linked to each other via

• A date table (which converts the date in reporting period), 
• Contracts table, which contains the supplier name, contract id and type of contract (technology, design etc)

What i am trying to acheive is as follows

1. look at my submission schedule
2. compare against my received table (D365)
3. itentify the status
   1. count Submission table reporting period - distinct count (as there will be a line for each metric) received table (D365) reporting period
      • 1 = within submission table, not within received table (D365)
      • 0 = within both tables
      • -1 = Within received table (D365), not expected within submission table.

what we are trying to achieve is how much work is left for the team to action. I f there is another way im missing, please let me know, i thought this may be the easiest way as both will have the reporting period in the same format.

Thanks again

[PC2790]

As per my understanding that I am able to get with the provided information, I would suggest to create a unique column which will be used to for a common attribute in the Received and Reporting Table.

I created a new column in both the tables as:

Contract_type = CONCATENATE('Reporting Schedule'[Contract Number],'Reporting Schedule'[Type])

Contract_type = CONCATENATE('Received'[Contract Number],'Received'[Type])

And then based on this value, I cretaed another column to trace the status. The logic goes as below:

If there is any corresponding value in Received table, that means the order has been recieved else it will mark as not received. DAX used as below:

Contract_Status = if(not(ISBLANK(LOOKUPVALUE('Reporting Schedule'[Contract_type],'Reporting Schedule'[Contract_type],'Reporting Schedule'[Contract_type]))),"Received","Not yet received")

Please suggest if this particular solution solves your purpose.

Thanks

[Anonymous]

Hi @AlB & @PC2790 

Thank you for the responses. I have updated the dax slightly, as i released the reporting schdule would only have 1 value and the recieved file will contain mutiple and require distinctcount i beleive (a line for each metric), however still experieing some issues.

Status = 

SUMX('Reporting Schedule', 
(COUNT('Reporting Schedule'[Expected date]))- 
(DISTINCTCOUNT('Received'[Reporting Period]))
)

Links to the files below, the relationships im fear i am stuck with as there are a lot more tables linked in to this file as it pulls data from 3 main source (Dynamics for the performance data and 2 master tables which are excel based at present for finance) 

Power BI 

Example Source File

Folder

Actual file relationship 

Attached file

• Received = A_Combined
• Date = M_Date_table
• reporting schedule - Same
• Work Task = Contracts

[PaulDBrown]

@Anonymous 

I'm not too sure what exactly you are trying to identify. I have built the following under the assumption that you are comparing Contracts, type and periods between the two tables:

1) To calculate Contracts Scheduled but not received:

Scheduled not received = 
VAR Sched = SELECTCOLUMNS('Reporting Schedule', "Contract", 'Reporting Schedule'[Contract Number], "Type", 'Reporting Schedule'[Type], "Period", 'Reporting Schedule'[Reporting Period])
VAR _Received = SELECTCOLUMNS('Received', "Contract", 'Received'[Contract Number], "Type", 'Received'[Type], "Period", 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(Sched, _Received))

2) To calculate contracts scheduled & received:

Scheduled & received = 
VAR Sched = SELECTCOLUMNS('Reporting Schedule', "Contract", 'Reporting Schedule'[Contract Number], "Type", 'Reporting Schedule'[Type], "Period", 'Reporting Schedule'[Reporting Period])
VAR _Received = SELECTCOLUMNS('Received', "Contract", 'Received'[Contract Number], "Type", 'Received'[Type], "Period", 'Received'[Reporting Period])
RETURN
COUNTROWS(INTERSECT(Sched, _Received))

3) To calculate contracts received but not scheduled:

Received not Scheduled = 
VAR Sched = SELECTCOLUMNS('Reporting Schedule', "Contract", 'Reporting Schedule'[Contract Number], "Type", 'Reporting Schedule'[Type], "Period", 'Reporting Schedule'[Reporting Period])
VAR _Received = SELECTCOLUMNS('Received', "Contract", 'Received'[Contract Number], "Type", 'Received'[Type], "Period", 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(_Received, Sched))

Which gets you this:

[Anonymous]

Hi @PaulDBrown 

I think I may have over complicated in the first request

We have a submission tracker when submissions are to due to be received, the column header is the reporting period

We have database of what is received.

These are linked to each other by the contract number (work task) and a date table.

We are trying to confirm

What is in the submission tracker and present

• Total numbers of what is due for the period (each contract following different reporting schedules
• The number of submissions that have been received
• The number outstanding
• The number which we unexpected and submitted to the database incorrectly.

The URN between each table is the Contract number / Work task

 

We are only interested if we have received a submission, not the volume of submissions received in any given month that match the URN.

Using the example:

• We have the type (Prog) Shoes, trainers & pumps.
• Contract name (scheme) as Dr martins, Nike, Cons

The schedule tables confirm when we would be expecting a delivery / submission. The received table (the database) confirms when the delivery has been received and the number of items (Metrics).

Is it possible using measures to

• Identify the Total numbers of what is due for the period (each contract name)
• The number of submissions that have been received
• The number outstanding
• The number which were unexpected and submitted to the database incorrectly.

I have updated the source files to hopefully paint a better picture

Power BI 

Example Source File

Folder

[PaulDBrown · Accepted solution]

@Anonymous 

Ok, see if this is what you need.

BTW, the relationships between your Date table and the fact tables are many-to-many (which can generate unexpected results and should be avoided).
Since we are looking at periods, I'va added a Period Table as a dim table for both fact tables to be used in the visuals (thus avoiding the dreaded many-to-many relationships from the Date table).

The new measures used:
1) Outstanding:

Outstanding (New) = 
VAR Sched = SUMMARIZE('Reporting Schedule', 'Reporting Schedule'[Contract Number], 'Reporting Schedule'[Reporting Period])
VAR _Received = SUMMARIZE('Received', 'Received'[Contract Number], 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(Sched, _Received))

2) Received:

Received (New) = 
VAR Sched = SUMMARIZE('Reporting Schedule', 'Reporting Schedule'[Contract Number], 'Reporting Schedule'[Reporting Period])
VAR _Received = SUMMARIZE('Received', 'Received'[Contract Number], 'Received'[Reporting Period])
RETURN
COUNTROWS(INTERSECT(Sched, _Received))

3) Unexpected:

Unexpected New = 
VAR Sched = SUMMARIZE('Reporting Schedule', 'Reporting Schedule'[Contract Number], 'Reporting Schedule'[Reporting Period])
VAR _Received = SUMMARIZE('Received', 'Received'[Contract Number], 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(_Received, Sched))

And this is the end result:

See if this is the end result you need.

Attached is the new PBIX file

[Anonymous]

This is great, i start to understand it a bit more now. it is a steep learning curve which i learning more by each day.

Thank you, i really appreciate the help.

Lee

[PC2790]

Hi @Anonymous ,

The logic of DAX expression that you have applied looks good and should solve the purpose, however it will fail in various conditions like: null or blank values in any of the columns

Please provide the sample data for trying the implementation. Also, please explain clearly what issue you are facing here.

[AlB]

Hi @Anonymous 

Can you show a sample of your 2 tables?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers