Need help summarizing the date

[abhishek_2593]

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

Data sample is something like this

Thanks in advance

[johnt75 · Accepted solution]

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.

[Anonymous]

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 · Accepted solution]

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.

[johnt75 · Accepted solution]

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.