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 dat...
Need Help
Tips and Tricks
  • 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)))
lbendlin's avatar
lbendlin
Super User
4 years ago

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
    4 years ago

    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
      4 years ago

      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".

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hey lbendlin,
        Thanks for your answer!
        That is the whole thing, that i do need a full usage of DAX without creating calculating columns, only based on measures. PQ solution does not meet my performance expectations 😕

        And the output required is simple card visual with Average time or sth like : 6.7 Days