Forum Discussion

saj's avatar
saj
Helper III
5 years ago
Solved

Continuous data from formatted date

I have a date table but any formatted dates are immediately interpreted by Power BI visuals as categorical data rather than continuous data. They are still continuous data even if they are for example academic months/years or financial year/month.

 

Continuous data allows me to add trend lines and keep the data within the visual rather than possible scroll bars when using categorical x axis.

 

Is there any way of displaying dates (maintaining continuous) but formatting them as appropriate?

I know I can format dates simply eg MMMM YYYY.

 

Thanks

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, saj 

    According to your description, it seems that the “Drill down” function in the Power BI can roughly meet your requirement, you can try my steps:

    1. Right-click on the [Finscial year] column, create a hierarchy for it:

     

    1. Right-click on the [Date] column, add it to the Financial year hierarchy:

     

    1. Go to the line chart and place the Finscial year hierarchy as the X-Axis of it:

     

    Then you can click on the “Drill down” to see the detailed data under the hierarchy of the Financial year.

    Drill mode in a visual in Power BI

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey saj ,

     

    the formatting doesn't make a big difference.

    Can you make sure the date column you are using is from a proper date table. So every date just appears once and there is no gap. Also make sure the column you are using is in the format date:

     

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • saj's avatar
      saj
      Helper III
      5 years ago

      Hi selimovd that's not what I meant.

       

      If the x-axis is a date then it is automatically continuous. There are a few options of how to format the date so it is user fieldly for the report consumer. In a date table various fields are obviously categorical (eg day of week or month of year). But if some columns are essentially just a formatted date but they get interpretted as text (and so categorical)

       

      The labels are categorical but the values are continuous.

       

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    Hi, saj 

    According to your description, it seems that the “Drill down” function in the Power BI can roughly meet your requirement, you can try my steps:

    1. Right-click on the [Finscial year] column, create a hierarchy for it:

     

    1. Right-click on the [Date] column, add it to the Financial year hierarchy:

     

    1. Go to the line chart and place the Finscial year hierarchy as the X-Axis of it:

     

    Then you can click on the “Drill down” to see the detailed data under the hierarchy of the Financial year.

    Drill mode in a visual in Power BI

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • saj's avatar
      saj
      Helper III
      5 years ago

      Thanks v-robertq-msft 

      No that's not what I need and I'm comfortable with hierarchies.

       

      Thanks