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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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

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

View solution in original post

12 REPLIES 12
lbendlin
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.
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
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

 

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

 

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.

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 

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

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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.