Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
Solved! Go to 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.
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.
Proud to be a Super User!
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.
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.
Proud to be a 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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
74 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |