Forum Discussion
Working with multiple date keys in table
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)))
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 ,
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.
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.