topic Re: Use Datediff function in two tables, which located in unrelatet tables in DAX Commands and Tips

Use Datediff function in two tables, which located in unrelatet tables

Talal141218 — Thu, 07 Nov 2024 09:47:28 GMT

Hello Profis,

i need your help to create a measure to subtract two dates in days, which located in tabels no realtet between them.

Please find the screen shot maybe help you to solve my issue. In addtion i want to create this measure in Fact_SalesOrderLine as you see in the Pic.

Please if you need more infos, don't hesitate to contect me.

Thanks in Advance.

Re: Use Datediff function in two tables, which located in unrelatet tables

ValtteriN — Thu, 07 Nov 2024 10:56:51 GMT

Hello,

You can calculate the dates you want to compare in variables and then place them in datediff.

e.g. Here I have a custom condition that date2 needs to be in year 2023, date1 has no conditions:

DDiff no relation =

var _date1 = MAX(GantDemo[E_date])

var _date2 = CALCULATE(MAX('Calendar'[Date]),YEAR('Calendar'[Date])=2023)

RETURN

datediff(_date1,_date2,DAY)

The tables are not related. You can add whatever conditions you need in the variable calculations (e.g. id needs to be some specific id).

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Use Datediff function in two tables, which located in unrelatet tables

Talal141218 — Thu, 07 Nov 2024 13:54:48 GMT

Hello,

Firstful thanks alot for your response,and i have results now but those are neither corresponding nor logic.

Re: Use Datediff function in two tables, which located in unrelatet tables

Anonymous — Fri, 08 Nov 2024 02:17:20 GMT

Hi Talal141218 ,
According to your description, after you arrive at the results, there may be data mismatches in the results, or the calculated values do not make logical sense, I try to reproduce your problem with some sample data, I hope to provide some ideas for the problems you encountered!
My sample data is calculating the difference between the date of each sales order and the date of the latest purchase order, and in the case where I am using the error code , although the value is returned, there is a logic error, as detailed below:

Order corresponds to multiple Purch values, and since we don't have a relationship, we need the other columns to help us make sure that there are no duplicate values, and we can get the correct value.

DateDiffMeasure = VAR OrderDate = MAX(Sales[OrderDate]) VAR ClosestPurchaseDate = CALCULATE( MIN(Purch[PurchaseDate]), FILTER( Purch, Purch[PurchaseDate] <= OrderDate // Ensure that the purchase date is before the order date && Purch[Index] =MAX('Sales'[OrderID]) // Ensure that each order is uniquely matched ), TOPN(1, Purch, Purch[PurchaseDate], DESC) // Get the most recent PurchaseDate ) RETURN IF( NOT(ISBLANK(ClosestPurchaseDate)), ABS(DATEDIFF(OrderDate, ClosestPurchaseDate, DAY)), BLANK() )

If you still have questions you can check out my pbix, I hope my answer can further answer your questions and I would be honored if I can solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.