Forum Discussion
Calculate values based on difference between 2 Dates
Hey Guys
Need some help with calculating values based on difference between 2 dates.
Data: My data is coming from 3 different tables.
Table 1: This has got "Planned Delivery Date" of the orders
Table 2: This has the "Actual Delivery Date" of the orders
Table 3: This has got value of orders in terms of Qty, Area(m2), Weight(Kgs) etc.
Required Output: I'm after Sum of quantity which were delivered late (i.e. Actual delivery date is greater than planned delivery date), Sum of qty which were Ontime (Planned delivery date = Actual delivery date) & Sun of qty which were early (Actual delivery date < Planned delivery date).
Will be good to have this analysis in charts & tables.
Understand having dates in 2 different tables is tricky, so hoping If you guys can help out.
Thanks
Tarun-Bhatia , One way is populate date from one table 2 another , asuume order no or some key is there
new columns
Table 1 Actual Delivery Date = maxx(filter(Table2, Table2[order no] = table1[order no]),Table2[Actual Delivery Date])
Table 3 Actual Delivery Date = maxx(filter(Table2, Table2[order no] = table3[order no]),Table2[Actual Delivery Date])
Table 3 Planned Delivery Date = maxx(filter(Table1, Table1[order no] = table3[order no]),Table2[Planned Delivery Date])
You can take date diff as columns now .
Or refer how to take across table date diff
refer if need
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
5 Replies
Tarun-Bhatia , One way is populate date from one table 2 another , asuume order no or some key is there
new columns
Table 1 Actual Delivery Date = maxx(filter(Table2, Table2[order no] = table1[order no]),Table2[Actual Delivery Date])
Table 3 Actual Delivery Date = maxx(filter(Table2, Table2[order no] = table3[order no]),Table2[Actual Delivery Date])
Table 3 Planned Delivery Date = maxx(filter(Table1, Table1[order no] = table3[order no]),Table2[Planned Delivery Date])
You can take date diff as columns now .
Or refer how to take across table date diff
refer if need
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8Thanks for quick reply Amit,
Unfortunately Table 1 & Table 2 are just "Date Tables" with Date dimensions of Planned_Delivery_Date(Table 1) & Actual_Delivery_date (Table 2).
In data model, both are linked to "Table 3" which has all the details about Order_Number, Customer, Qty, Sales etc etc!!
Suggestions?
Thanks
Tarun-Bhatia , share some sample in table format
Forgot to mention
Table:3 has got "Planned_Date_ID" & "Delivered_Date_ID" which is used to link between Planned date in table 1 & Delivery date in table 2.
Maybe we could use that some way
Thanks
