Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
abhishek_2593
Advocate I
Advocate I

Need help summarizing the date

I have a data set as follows with multiple orders with same date but different time stamps and when i am creating a table visual to calculate day-by-day sales or cumulative sales, I'm getting the output with multiple rows for same date instead of one row for each date under each category field that i am calculating.

 

is there any alternate method to summarize the data? i.e., 1 row for 1 date etc?

 

For Ex - Cat 1 should have value 20 under date 01/19/2024 instead of multiple records like this

abhishek_2593_0-1736337390487.png

 

Data sample is something like this

abhishek_2593_1-1736337537352.png

 

Thanks in advance

 

2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

If you don't need the time part of the order date for anything else then get rid of it. In Power Query change the type to be date rather than date time. Its important that you change the type in Power Query and not just in Power BI Desktop, as otherwise it will retain the time information.

If you do need the time part for other reporting then create a new column in Power Query by extracting just the date part, and use the new date column to link to your date table or in your visual.

View solution in original post

danextian
Super User
Super User

Do as @johnt75  said. Even if you use a separate dates table and relate it to your fact table, you'll only see blank rows because the relationship is between a date column and a datetime column. Splitting a datetime column into separate date and time columns or keeping just the date can reduce the cardinality and improve the efficiency of your data model.










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

3 REPLIES 3
v-yilong-msft
Community Support
Community Support

Hi @abhishek_2593 ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

 

 

Best Regards

Yilong Zhou

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

danextian
Super User
Super User

Do as @johnt75  said. Even if you use a separate dates table and relate it to your fact table, you'll only see blank rows because the relationship is between a date column and a datetime column. Splitting a datetime column into separate date and time columns or keeping just the date can reduce the cardinality and improve the efficiency of your data model.










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
johnt75
Super User
Super User

If you don't need the time part of the order date for anything else then get rid of it. In Power Query change the type to be date rather than date time. Its important that you change the type in Power Query and not just in Power BI Desktop, as otherwise it will retain the time information.

If you do need the time part for other reporting then create a new column in Power Query by extracting just the date part, and use the new date column to link to your date table or in your visual.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.