Hi,
I have two tables. First table contains workorder information. Second table contains material transations information. Join is on workorder ID and operation sequence number. I try to compare the release date from the workorder table with the MINIMUM transaction date from the materials transactions table. Return should be the number of days difference. For some reason I cannot compare both dates in the different tables, Power BI does not find direct relationship. Can anyone help?
Thanks in advance.
Hi @Hyperchef1969
can you provide the sample data for both the tables??
would be easier for us to give you proper answer.
Hi @Hyperchef1969
No problem.
Can you provide the just sample data take same no of columns and change the values of a columns. And share both the files just screen shot of 4 to 5 records.
Hi, see both tables for an example. Join_ID 5465083-100 can be used as an example. Once listed in the first table, multiple times in the second (transaction table). Outcome should be days difference between the minimum transaction date of the second table and the release date of the first table, by Join ID. Thanks in advance
Please create the following measure:
difference = Datediff( Min(Transcation date), Max(Release date),day)
Let me know whether you are getting desired result of not.
If my post helps please give kudos and accept it as a solution!
Thanks
Hi Uzi2019,
Was able to get this working, but facing the problem that the days include non working days. Added new working calender table to datamodel, linked on transaction date and working calender date. Extending the formula to 'CALCULATE(MIN(TRANSACTION DATE), MAX(RELEASE DATE),DAY), working CALENDER DATE = "Y") does not exclude non working days. Formula not ok? Or what do you think is not ok.
