Skip to main content
Showing results for 
Search instead for 
Did you mean: 

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

Not applicable

Working with multiple date keys in table



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 = 


max('DWH DIM_CLAIM'[ClaimCreateDate]),
max('DWH DIM_CLAIM'[ClaimClosedDate]),day)












 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

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.


 DATEVALUE(Left([CloseDateKey],4) & "-" & MID([CloseDateKey],5,2) & "-" & RIGHT([CloseDateKey],2))
-DATEVALUE(Left([CreateDateKey],4) & "-" & MID([CreateDateKey],5,2) & "-" & RIGHT([CreateDateKey],2)))

View solution in original post

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

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

Not applicable

Hi @lbendlin ,

Thanks for your response.

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

Thanks for your help!

Sorry for mentioning USERELATIONSHIP - it is not required at all.  You can do the math in Power Query


    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}})
    #"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".

Not applicable

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

In DAX you can use the same approach.


 DATEVALUE(Left([CloseDateKey],4) & "-" & MID([CloseDateKey],5,2) & "-" & RIGHT([CloseDateKey],2))
-DATEVALUE(Left([CreateDateKey],4) & "-" & MID([CreateDateKey],5,2) & "-" & RIGHT([CreateDateKey],2)))
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.

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.

Not applicable


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.

Not applicable

Aight, so userelationship won't solve this. thanks

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.

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!

Helpful resources

Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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