Forum Discussion
Working with multiple date keys in table
Hello,
I am having in table A two date keys formatted as YYYYMMDD (CreateDateKey and CloseDateKey).
There is another entity called Calendar with DateKey formatted also YYYYMMDD and Date in date format.
My goal is to calculate Average time to Finish in days which is Average of (CloseDate - CreateDate ---> time taken to finish).
I was able to perform this creating additional columns CreateDate and CloseDate using left&right functions, substracting dates with datediff(measure) and then performing averagex on datediff measure as below. I did not even use Calendar table for this.
Average time to complete a task =
VAR TIMETAKENTOFINISH =
datediff(
max('DWH DIM_CLAIM'[ClaimCreateDate]),
max('DWH DIM_CLAIM'[ClaimClosedDate]),day)
RETURN
AVERAGEX('DWH DIM_CLAIM',TIMETAKENTOFINISH)
Solution above works, but requires adding two date columns based on date keys.
(which are created like
date(left('DWH DIM_CLAIM'[ClaimCreateDateKey],4),
right(left('DWH DIM_CLAIM'[ClaimCreateDateKey],6),2),
right('DWH DIM_CLAIM'[ClaimCreateDateKey],2))
and it creates date column based on date key)
My question is, is there a way to have the same result without adding additional columns (transforming datekey to date - which is probably unefficient)? I found a USERELATIONSHIP function but did not find a solution using it.
Thanks in advance!
Example data Attached:
Table A Lets say Claim Handling:
| Primary Key | CreateDateKey | CloseDateKey |
| 1 | 20220120 | 20220122 |
| 2 | 20220105 | 20220120 |
| 3 | 20220106 | 20220120 |
| 4 | 20220114 | 20220120 |
| 5 | 20220115 | 20220120 |
| 6 | 20220116 | 20220120 |
| 7 | 20220119 | 20220120 |
Table B as Calendar
| DateKey | Date (date format) |
| 20220101 | 1 Jan 2022 |
| 20220102 | 2 Jan 2022 |
| 20220103 | 3 Jan 2022 |
| 20220104 | 4 Jan 2022 |
| 20220105 | 5 Jan 2022 |
| ... | ... |
| 20220129 | 29 Jan 2022 |
| 20220130 | 30 Jan 2022 |
| 20220131 | 31 Jan 2022 |
Expected output is measure that:
>Relates Table A(Claim handling) DateKeys with TableB.
>Subtract the days between dates.
>Performs Average of subtracted values.
In this example, output will be like:
Averagex of (CloseDateKey - CreateDateKey) ----> Averagex of (2, 15, 14, 6 , 5, 4, 1), because (Primary Key 1) is (22/01/22 - 22/01/20 is 2, 22/01/20 - 22/01-05 is 15, etc.)
This example output should be averagex(2,15,14,6,5,4,1) = 6,7
In DAX you can use the same approach.
Measure = AVERAGEX('DWH DIM_CLAIM', DATEVALUE(Left([CloseDateKey],4) & "-" & MID([CloseDateKey],5,2) & "-" & RIGHT([CloseDateKey],2)) -DATEVALUE(Left([CreateDateKey],4) & "-" & MID([CreateDateKey],5,2) & "-" & RIGHT([CreateDateKey],2)))
12 Replies
- lbendlinSuper User
Yes, USERELATIONSHIP is recommended here.
Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- AnonymousNot applicable
Hi lbendlin ,
Thanks for your response.
Ive updated post message above with sample data and expected output.Thanks for your help!
- lbendlinSuper User
Sorry for mentioning USERELATIONSHIP - it is not required at all. You can do the math in Power Query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDIyMDQyQDCNlGJ1opWM4AIGpsjKQHLGCDkzdDkTuIChCbocwiBDDDMRBhlimGmOkLNEkYsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Key" = _t, CreateDateKey = _t, CloseDateKey = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.From([CloseDateKey])-Date.From([CreateDateKey])), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}) in #"Changed Type"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".