Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 KeyCreateDateKeyCloseDateKey
12022012020220122
22022010520220120
32022010620220120
42022011420220120
52022011520220120
62022011620220120
72022011920220120

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

  • lbendlin's avatar
    lbendlin
    4 years ago

    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

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi lbendlin ,

      Thanks for your response.

      Ive updated post message above with sample data and expected output.

      Thanks for your help!

      • lbendlin's avatar
        lbendlin
        Super 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".