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

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!