Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 dat...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
lbendlin
Super User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    Sure 

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago
    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's avatar
    Anonymous
    Not applicable
    4 years ago

    Aight, so userelationship won't solve this. thanks

  • grantsamborn's avatar
    grantsamborn
    Solution Sage
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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!