Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table that looks like below:
My question will be 2 parts.
I originally had a DATEDIFF, DAX between Order Date and Ship Date. Then created a AVG for the DATEDIFF, and this was based on the number of lines. However, I would like to only see the AVG DATEDIFF for Unique PO #.
In the example, it would be the AVG of 2 PO #, instead of the 5 lines.
Secondly, how do I create a DATEDIFF for the unique PO #, to only use the latest ship date.
Example, for PO #123, it would be DATEDIFF(1/1/2019,1/3/2019) and for PO#124, DATEDIFF(2/2/2019,2/6/2019)
Thanks,
Solved! Go to Solution.
Hi,
You can create measure Latest Ship Date first, then create measure DateDiff .
Latest Ship Date = CALCULATE(MAX(Table1[Ship Date]),FILTER(ALL(Table1),Table1[PO #]=MAX(Table1[PO #])))
DateDiff = CALCULATE(DATEDIFF(MAX(Table1[Order Date]),[Latest Ship Date],DAY),FILTER(Table1,Table1[PO #]=MAX(Table1[PO #])))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @PBIUWO ,
You can create measures like DAX below.
Average DATEDIFF = SUMX(Table1,DATEDIFF(Table1[Order Date],Table1[Ship Date],DAY))/DISTINCTCOUNT(Table1[PO #])
DateDiff = CALCULATE(DATEDIFF(MAX(Table1[Order Date]),MAX(Table1[Ship Date]),DAY),FILTER(Table1,Table1[Ship Date]=LASTDATE(Table1[Ship Date])&&Table1[PO #]=MAX(Table1[PO #])))
DateDiff for latest ship date =
VAR _table = SUMMARIZE(Table1, Table1[PO #],"_Value",[DateDiff])
RETURN
IF(HASONEVALUE(Table1[PO #]),[DateDiff],SUMX(_table,[_Value]))
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZs99glFqZpJu5u0s3...
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xicai ,
Really appreciate this!
However, on the DATEDIFF measure I am getting this error. As result, latest ship measure is also an erorr.
"Error Message:
MdxScript(Model) (4, 154) Calculation error in measure 'YTD'[DateDiff]: A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.
"
*YTD is the name of your Table1
Hi @PBIUWO ,
Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
Hi,
You can create measure Latest Ship Date first, then create measure DateDiff .
Latest Ship Date = CALCULATE(MAX(Table1[Ship Date]),FILTER(ALL(Table1),Table1[PO #]=MAX(Table1[PO #])))
DateDiff = CALCULATE(DATEDIFF(MAX(Table1[Order Date]),[Latest Ship Date],DAY),FILTER(Table1,Table1[PO #]=MAX(Table1[PO #])))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |