Working with multiple date keys in table

[Anonymous]

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

[lbendlin]

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

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]

Hi @lbendlin ,

Thanks for your response.

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

Thanks for your help!

[lbendlin]

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]

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

[lbendlin · Accepted solution]

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)))

[Anonymous]

@lbendlin ,

That works perfectly fine! Datevalue was a function i did not know about.

One more thing is that there is a reason that Calendar table exists. 
Is there an approach, where we can use the Calendar table in order to actually work on relationships/Keys. It is just due to some CloseDateKey's that are not date yet (-1 key is a blank value in calendar table, meaning something did not Finish/Close yet). What do you think?
If it does not sound possible/reasonable i will accept ur solution above.

I believe that I could also make a condition with ur latest measure to avoid counting if CloseDateKey is -1, but for some learning purposes i'd like to know if there is a way to perform this using keys/relationships as mentioned paragraph above.

[lbendlin]

This is going into outer join and USERELATIONSHIP territory.  Without good sample data ( and a bit more context on what you are trying to achieve and what the expected result should look like ) it will be difficult to help more.

[Anonymous]

Sure 

[lbendlin]

1. Find Date in Calendar table by DateKey for CreateDateKey and CloseDateKey.

It's either or. You can't have both dates linked with the calendar table in an active relationship. One of them has to be inactive.

[Anonymous]

Aight, so userelationship won't solve this. thanks

[grantsamborn]

What about 2 date tables?  CreatedDate and ClosedDate

Regarding records with no closed date:  Would it help to use PowerQuery to replace nulls with the Datekey for the CURRENT DATE?

If so, consider trying this.

pbix: https://1drv.ms/u/s!AnF6rI36HAVkhPFMNE3v3SoIIN_0AQ?e=vygzJ2

Apologies:  I could have used MUCH simpler date tables but I usually get lazy and use an M script I have.  Your date table should work.

[Anonymous]

Thats completly fine. But i got my take more clearly now. 

My idea is to relate two datekeys with calendar table within measure. You could imagine this by let's say that datekeys would be smh like NOT 20220813 is a 8/13/2022 but lets say key 1 would be 8/13/2022 key 2 8/14/2022 etc. 

But yeah, in my scenario datekys are 'transformable' into date so it aint a problem.
Thanks for help guys!