cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
Super User

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 12
Super User

Yes, USERELATIONSHIP is recommended here.

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.

Anonymous
Not applicable

Hi @lbendlin ,

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

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]),
#"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
Not applicable

Hey @lbendlin,
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

Super User

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
Not applicable

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

Super User

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
Not applicable

Sure

Super User
``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
Not applicable

Aight, so userelationship won't solve this. thanks

Solution Sage

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.

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
Not applicable

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors