Forum Discussion
Cross Table DateDiff with Multiple Dates in One Column
Hi,
I have data on purchase order Order Date and Receive Dates and I'm trying to calculate the difference between the dates. There are multiple items on the purchase order so the PO Order Date is a single date (all items were ordered on the same day) but the Receive Date is different for each line (each item was delivered on different dates) on the purchase order.
Is there a way of getting datediff to work using this data to give me the Elapsed Days?
When I try to use datediff it tells me that because the Receive Date column has multiple values the datediff won't work. I can't use an aggregate (max, min etc.) as I need to know the Elapsed Days for each line.
This is my statement so far:
Elapsed_Days = datediff(pupo[po_date], related(mtns[rcv_date], DAY))
The tables pupo and mtns are linked:
Thanks, Antony.
Hi Anonymous
If your raw data is like below, you can add a column by combining Order number column and Item ID column like Order Item ID = Orders[Order Number] & "-" & Orders[Item ID] in both tables, then create relationship between them on these new columns. In this way, this relationship is one-to-one and helps avoid multi-mapping error.
Then create measure:Date Diff = DATEDIFF(SELECTEDVALUE(Orders[Order Date]),SELECTEDVALUE(Receives[Receive Date]),DAY)If you already have other active relationship between these tables, you can make above relationship inactive and then modify the measure like below. As a result, the inactive relationship will only be applied in this measure.
Date Diff = CALCULATE(DATEDIFF(SELECTEDVALUE(Orders[Order Date]),SELECTEDVALUE(Receives[Receive Date]),DAY),USERELATIONSHIP(Orders[Order Item ID],Receives[Order Item ID]))Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
6 Replies
Hi Anonymous
If your raw data is like below, you can add a column by combining Order number column and Item ID column like Order Item ID = Orders[Order Number] & "-" & Orders[Item ID] in both tables, then create relationship between them on these new columns. In this way, this relationship is one-to-one and helps avoid multi-mapping error.
Then create measure:Date Diff = DATEDIFF(SELECTEDVALUE(Orders[Order Date]),SELECTEDVALUE(Receives[Receive Date]),DAY)If you already have other active relationship between these tables, you can make above relationship inactive and then modify the measure like below. As a result, the inactive relationship will only be applied in this measure.
Date Diff = CALCULATE(DATEDIFF(SELECTEDVALUE(Orders[Order Date]),SELECTEDVALUE(Receives[Receive Date]),DAY),USERELATIONSHIP(Orders[Order Item ID],Receives[Order Item ID]))Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.v-jingzhangThanks so much for this, that worked really well.
Anonymous , try a measure like
Elapsed_Days = sumx(values(pupo[pono]),datediff(min(pupo[po_date]), max(mtns[rcv_date]), DAY))
Hi Anonymous ,
There is an existing thread for this similar issue on this forum:
https://community.powerbi.com/t5/Desktop/Cross-table-Datediff/m-p/81075
Thanks,
Pragati
Hi Pragati11,
Yes, I've already tried their solution and whilst there are no errors, I get a blank column:
