Forum Discussion

dawcza3's avatar
dawcza3
Frequent Visitor
9 years ago
Solved

Calculate Age with Direct Query Support

 

I'm trying to calculate Age from DateOfBirth. Actually I'm do this with transformation date to age and round down. It's works ok, but it not avalaible to use data in direct query mode. So, is there any other way to calculate age which support direct query mode ?

Need Help
Tips and Tricks
  • dawcza3's avatar
    dawcza3
    9 years ago

    v-ljerr-msft

     

    thanks for replay but actually I do this in simple way :

    Age = ROUNDDOWN(((1,0*(TODAY()-Partner[DateOfBirth]))/365);0)

4 Replies

  • dawcza3's avatar
    dawcza3
    Frequent Visitor
    9 years ago

    v-ljerr-msft

     

    thanks for replay but actually I do this in simple way :

    Age = ROUNDDOWN(((1,0*(TODAY()-Partner[DateOfBirth]))/365);0)
    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi dawcza3,

       

      Nice solution!

       

      Could you accept your reply as solution to help others who may have similar issue easily find the answer and close this thread?:smileyhappy:

       

      Regards

      • Brian820's avatar
        Brian820
        New Member
        9 years ago

        Noob question, my apologies. I have a very similar need and don't understand where I would define "Age" so that it can be included in the visualization. Is this a new column, or a measure?

         

        Secondly, could it be applied to a summarized result. Using this example, to find the age of the youngest person in a family?

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi dawcza3,

     

    According to your description above, you should be able to use the formula below to create a calculate column to calculate age in direct query mode.:smileyhappy:

    Age =
YEAR ( TODAY () ) - YEAR ( Table1[DateOfBirth] )
    + IF (
        DATE ( 1900, MONTH ( TODAY () ), DAY ( TODAY () ) )
            < DATE ( 1900, MONTH ( Table1[DateOfBirth] ), DAY ( Table1[DateOfBirth] ) ),
        - 1,
        0
    )

    Regards