cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rangerrick9211 Frequent Visitor
Frequent Visitor

How to treat date format as numbers/integer while retaining date format?

Hi - I do a lot of monte carlo work with schedules where dates are the data we're counting accross the iterations. In Excel, I'm able to treat the date format as an interger, knowing the date value integer is behind it, in typical formulas like quartile and median to develop box plots. 

 

I would really like to move into Power BI to streamline the workflow with the off-the-shelf box and violin plot vizs. However, no matter the date format, I can't treat it as an integer. Only when I convert to it's numerical underlying via simple data type "Date" to "Whole Number" or using the datevalue forumula. However, then my axis is numbers, not dates and illegible. Any thoughts? Thanks!

4 REPLIES 4
Super User
Super User

Re: How to treat date format as numbers/integer while retaining date format?

HI @rangerrick9211,

 

Not sure of what you really want to achieve, some things can be achieved using measures or similar, but why don't you just simplify this and create a new column that is a copy of your Date column and convert it to numerical, then use this column as your values to have the quartiles and median and the original date column as your axis?

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




rangerrick9211 Frequent Visitor
Frequent Visitor

Re: How to treat date format as numbers/integer while retaining date format?

Appreciate the reponse!

 

Simply, neither the box and whisker nor violin plot visualizations from the Power BI app store have formattable axis options. They're fixed to the data. I assume it's easier to format the data than re-invent the charts just have axis formatting available.

Super User
Super User

Re: How to treat date format as numbers/integer while retaining date format?

Hi @rangerrick9211,

 

Not really sure if I understand what you are referring, I have made a simple test with some basic data and made a Date Column and a copy of that column with number. Used the Date column as my Category in the Box and Wiskers chart an I add the Date (month, year, quarter) in the axis and values used the values.

 

i don't use this type of chart and to what I can read this is a difficult chart to use based on the configurations.

 

Can you share some sample data and what you want to achieve and I can see if I can help you?

 

another option is to get in contact with the support of the creator of the box and whisker chart.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




dm-p Regular Visitor
Regular Visitor

Re: How to treat date format as numbers/integer while retaining date format?

Hi - I've just come across this while repsonding to another post. What are you specifically after with the axis formatting for the violin plot custom visual? I'm the author and might be able to help/have a look for you. Alternatively, feel free to create an issue in the repo and we can discuss there.