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