Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dataset record datastamp

Hi,

my dataset has "CREATEDATETIME" column that contain a hierarchy data with  Year,Quarter,Month and Day.

 

In records you can also see hours, minutes and seconds but i can't use them because i don't see them in data field (right screen). Why?

 

How can i add them and maybe Week,Semester and Four Months?

 

Thanks

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Besides solution above, you could create calculated columns directly

    year = YEAR([cretaedtime])

month = MONTH([cretaedtime])

day = DAY([cretaedtime])

weeknum = WEEKNUM([cretaedtime],2)

weekday = WEEKDAY([cretaedtime],2)

hour = HOUR([cretaedtime])

minute = MINUTE([cretaedtime])

seconds = SECOND([cretaedtime])

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    First of all, it should be noted that "hierarchy" here would not create time columns automatically.

     

    You could add these columns in Edit Queries

    With UI function as above, you could add "year","quarter",month","day","hour","minute","second",

    As for weeknum and weekday, you could add a custom column in Edit Queries with the following function:

    Date.DayOfWeek :Returns a number between 0 and 6 representing the day of the week in the provided datetime value
    Date.WeekOfYear:Returns a number from 1 to 54 indicating which week of the year the date

    You need click on "Apply&&Close" to save these changes into your model.

     

    As for waht you said like "Semester and Four Months", we could create calcuated columns using DAX fuction.

    If you have problem doing this, please provide clear syntax about them so i can figure out how to cretae this column.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    Besides solution above, you could create calculated columns directly

    year = YEAR([cretaedtime])

month = MONTH([cretaedtime])

day = DAY([cretaedtime])

weeknum = WEEKNUM([cretaedtime],2)

weekday = WEEKDAY([cretaedtime],2)

hour = HOUR([cretaedtime])

minute = MINUTE([cretaedtime])

seconds = SECOND([cretaedtime])

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

      i created new colums like you, can i put these colums in CREATEDATETIME hierarchy?

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous 

        As tested, i can't add "hours,,ect" columns in the hierarchy.

        Based on my experience, it doesn't matter when i use them together as a whole hierarchy.

        i can add them all in a matrix visual(add in Row field) or line/column/bar visual...(Add columns in X-axis)

        Then expand all down to one level or expand to next level.

        Best Regards
        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.