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
Anonymous
Not applicable

How to calculate the sum for different groups and date listed below(power query)

@Fowmy 

 

I'm curious if there is a way to modify the code below. I used it to extract the data from the Sales column for the current date into a new column, thanks to @Fowmy  but the problem is that it enters 0 for the current date even when i know there are values for the current date. I don't want to have to enter the dates manually, i want them autogenerated.  Thanks so much for helping.

 

= Table.AddColumn(#"Extract Week Before Previous Week", "Today", each if [TransactionDate] = DateTime.FixedLocalNow() then [Sales] else 0)

 

 

 

 

   
TransactionDate  ProductSales
7/17/2020 : 5:30:13 PM  Beans1
7/17/2020 : 7:45:29 AM  Beans0
7/3/2020 : 7:37:44 PM  Rice0
7/17/2020 : 5:37:44 AM  Fruit3
7/16/2020 : 7:37:44 AM  Rice0
7/5/2020 : 10:00:44 AM  Yam4
7/17/2020 : 04:37:19 PM  Fruit3
7/16/2020 : 11:37:14 PM  Fruit3
7/1/2020 : 5:37:13 AM  Yam1
7/15/2020 : 7:37:44 PM  Fruit0
7/4/2020 : 2:02:12 PM  Beans1
7/5/2020 : 7:37:44 AM  Beans0
7/16/2020 : 7:37:44 PM  Rice2
7/17/2020 : 5:27:09 PM  Beans0
7/16/2020 : 7:37:44 PM  Rice0
7/26/2020 : 6:20:11AM  Rice3
7/26/2020 : 7:37:44 PM  Rice4
7/16/2020 : 12:37:44 PM  Fruit8
7/16/2020 : 9:37:40 AM  Fruit9

 

 

way to calculate the Today column such that it doesn't show zero at all times for the current day even when it's not ended.  For example, I have in my data that there's been 2 items sold for today but regardless it still shows 0 for all the data in that column. Currently this is the formula in power query that I used according to your solution. Thanks.

 

= Table.AddColumn(#"Extract Week Before Previous Week", "Today", each if [Date] = DateTime.FixedLocalNow() then [Sold] else 0)

1 ACCEPTED SOLUTION
MattAllington
Community Champion
Community Champion

DateTime.FixedLocalNow() Returns dateTime, not a date. From memory, you need. 

DateTime.Date(DateTime.FixedLocalNow())



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

2 REPLIES 2
MattAllington
Community Champion
Community Champion

DateTime.FixedLocalNow() Returns dateTime, not a date. From memory, you need. 

DateTime.Date(DateTime.FixedLocalNow())



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
Anonymous
Not applicable

@MattAllington  Thank you so much, it worked like a charm.

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.