topic Re: Create column based on difference between two dates from two tables in DAX Commands and Tips

Create column based on difference between two dates from two tables

Swiftglade — Wed, 23 Jun 2021 16:55:23 GMT

Hi,

I am trying to create a Calculated column to track the difference between two dates from two different columns. The goal is to find when a shipment has been recieved (Final Merger) and when the product has been recieved and put away (Export Worksheet). I am trying to create a calcuated column using an if statement, however i run into the error.

The If statement is created within Export OR Final Merger

1. Wont allow me to select multiple tables in my If Statement. If I select either fields, I wont be able to select the other table.

2. Error - "Cannot Find (Column Name)

Tables

Table	Column	Type
Final_Merger	First Date	Date
Export Worksheet	Second Date	Date
Date Dimension	Date	Date

References

First Table	Second Table	Column	Relationship
Final_Merger	Export Worksheet	Receipt ID	Many to Many
Final Merger	Date Dimension	Date	Many to One

Re: Create column based on difference between two dates from two tables

Fowmy — Wed, 23 Jun 2021 17:06:59 GMT

Swiftglade

I suggest you avoid creating Many-to-Many relationships. If you can share the formula, it will be clear enough.

Re: Create column based on difference between two dates from two tables

Swiftglade — Wed, 23 Jun 2021 18:15:30 GMT

Hi Fowmy ,

Many to Many relationship comment-

I will look into cleansing the data, but we recieve it with values that have duplicates, from Receipt ID to another version of Receipt ID. At the moment, we cannot cleanse it and must have it as Many-Many.

Formula-

We are trying to get the First Date from "Final Merger", and compare the date to Export Worksheet.
So for example, We have "Frist Date, which is June 23. We want to know the when the next workday is,
so we created a column in our Date Table to have the next work day.
We then want to compare if the date the product was received (Second Date) is on time or not on time.
Our goal was to do an Greater than if statement.

Another example below.

IF(Second Date>Next Workday),"Shipment has passed date","Shipment was received on time")

Final Merger	Export Worksheet
First	Inventory Stored	Next Workday
2021-02-10 0:00	2021-02-11 0:00	2021-02-11 0:00