Forum Discussion
Working with dates
Hello,
I am relatively new to power BI. We're using a connector that pulls out Netsuite data in Azure blob storage once a day, making the data available to work with through Power BI. I am having difficulty working with dates. The dates on one of our custom records in coming in as date time and when added to a visualisation, I was expecting power BI to group the dates and display all April records in a single column on a bar chart. Instead power BI has not grouped the dates so we're ending up with a bar chart with a column for each daily number. I'm sure this something straight forward, can someone assist?
niallkellyyou should always have also a date column and going further also a date dimension, but let's say we are dealing now with just one table and you don't created a date dimension for it, than you have 2 options:
1) Add a date column in Power Query / Calculated Column in DAX and use it for the group by. Better also to just keep the time part in the date time column to have less granularity, very important to model size and can have positive impact on performance.
2) In case you have auto date time settings enabled in your file than you could do just add 2 times that column to your table and in one of them use the hierarchy Power BI created for you and in the other the column itself. Like this:
Option 1 is much much better 🙂In case it answered your question, please accept it as a solution to help the other members find it more quickly. Appreciate Your Kudos 💪
Showcase Report – Contoso By SpartaBI
Website Linkedin Facebook
This is SpartaBI!Hi niallkelly ,
Power BI provide a function to create a Date Hierarchy automaticlly. Please enable it.
File -> Options -> Date load ->Time intelligence
Then these date/time will be grouped.
And different connection patterns can lead to no hierarchy, for more details you can refer this blog.
Why there is no date hierarchy - Microsoft Power BI Community
Or you can create this hierarchy or group by yourself.
right click create hirerarchy or new group. By the way, new group can group date/time by days or hours.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
niallkellyyou should always have also a date column and going further also a date dimension, but let's say we are dealing now with just one table and you don't created a date dimension for it, than you have 2 options:
1) Add a date column in Power Query / Calculated Column in DAX and use it for the group by. Better also to just keep the time part in the date time column to have less granularity, very important to model size and can have positive impact on performance.
2) In case you have auto date time settings enabled in your file than you could do just add 2 times that column to your table and in one of them use the hierarchy Power BI created for you and in the other the column itself. Like this:
Option 1 is much much better 🙂In case it answered your question, please accept it as a solution to help the other members find it more quickly. Appreciate Your Kudos 💪
Showcase Report – Contoso By SpartaBI
Website Linkedin Facebook
This is SpartaBI!SpartaBI I'm missing something. I don't have a date hierarchy available to me. I was expecting for that when I started reviewing the data, this is what I see
I don't see the year etc. However if I pull the data in from excel I do, so I suspect there is some sort of restriction or control in place. As such I've escalated to the vendor who helped us set this up,
thanks,
SpartaBI I suspect it is the date format that may be causing this, the format of the date is like this 3/5/2022 12:56
Is there a way to get Power BI to group these dates and give thedate heirarchy?
niallkelly just to make sure, when you look at the data type in the column tools ribbon, do you see Date/time or text?
niallkelly can you share the file? 🙂 Through dropbox or access link to some drive?
Hi niallkelly ,
Power BI provide a function to create a Date Hierarchy automaticlly. Please enable it.
File -> Options -> Date load ->Time intelligence
Then these date/time will be grouped.
And different connection patterns can lead to no hierarchy, for more details you can refer this blog.
Why there is no date hierarchy - Microsoft Power BI Community
Or you can create this hierarchy or group by yourself.
right click create hirerarchy or new group. By the way, new group can group date/time by days or hours.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
