How to calculate the sum for different groups and date listed below

[Anonymous]

Hi,

How do i create two custom fields that calculate the sum of sales for the previous day and last week, this week, Today and by product. For example, today is 07/17/2020 so the previous calendar date will be 07/16/2020, last week sales date would be 07/05/2020 to 07/11/ 2020, this week would be 07/12/19  to 07/18/2020 and today would be 07/17/2020. I don't want to have to enter the dates manually, i want them autogenerated.  I am completely new to power query and have no idea what DAX does please help,  thanks so much for helping.

TransactionDate	Product	Sales
7/17/2020 : 5:30:13 PM	Beans	1
7/17/2020 : 7:45:29 AM	Beans	0
7/3/2020 : 7:37:44 PM	Rice	0
7/17/2020 : 5:37:44 AM	Fruit	3
7/16/2020 : 7:37:44 AM	Rice	0
7/5/2020 : 10:00:44 AM	Yam	4
7/17/2020 : 04:37:19 PM	Fruit	3
7/16/2020 : 11:37:14 PM	Fruit	3
7/1/2020 : 5:37:13 AM	Yam	1
7/15/2020 : 7:37:44 PM	Fruit	0
7/4/2020 : 2:02:12 PM	Beans	1
7/5/2020 : 7:37:44 AM	Beans	0
7/16/2020 : 7:37:44 PM	Rice	2
7/17/2020 : 5:27:09 PM	Beans	0
7/16/2020 : 7:37:44 PM	Rice	0
7/16/2020 : 6:20:11AM	Rice	3
7/17/2020 : 7:37:44 PM	Rice	4
7/16/2020 : 12:37:44 PM	Fruit	8
7/16/2020 : 9:37:40 AM	Fruit	9

[Fowmy · Accepted solution]

@Anonymous 

Here you go!

I added some additional data to test. Paste below code in a new blank query,
Check the completed PBI file: https://1drv.ms/u/s!AmoScH5srsIYgYF1HIrlsUkx7wkVrQ?e=Na2l88
Check the "Added Column" steps and group to learn how it was done.

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Anonymous]

Table.Group(
  tab, 
  {" Product"}, 
  {
    {
      "thiswk", 
      each List.Sum(Table.SelectRows(_, each Date.IsInCurrentWeek(_[TransactionDate]))[Sales])
    }, 
    {
      "prevwk", 
      each List.Sum(Table.SelectRows(_, each Date.IsInPreviousNWeeks(_[TransactionDate], 1))[Sales])
    }, 
    {
      "prevday", 
      each List.Sum(Table.SelectRows(_, each Date.IsInPreviousNDays(_[TransactionDate], 1))[Sales])
    }, 
    {
      "today", 
      each List.Sum(Table.SelectRows(_, each Date.IsInCurrentDay(_[TransactionDate]))[Sales])
    }
  }
)

[Anonymous · Accepted solution]

@Anonymous 

Thanks so much for responding but your code didn't run, I'm not sure if there are parts of it that needs to be edited that i didn't. Could you may point me in the right direction.  I edited the "tab" part of the code to reflect the name of the last task i performed but it didn't work. Thanks.

[Anonymous]

@Anonymous 

the code expects a table named tab, which has a column named " Product"  (be carefull out for any invisible spaces at the beginning or end of the name. I copied and pasted the data from your message) ; a column named  TransactionDate of type datetime and a column named Sales.

here the complete code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZI7D4MwDIT/SpQZCdsJj3ijQ7dKVbcKMaCKgaEd+vj/TVECBJJO8fDpLr5z28oqxyonIBAsClbAqMT5JDMpxGHoHy87oeyyEKxYF0xGNCEIDlQLpyyqveBlvA0rLDCeOKd3fH7Gt52UB8utYBMVLDyGwAAr7trf7at3vqB/emj8B9PGiBOpk2Qd7GJDDKznCItENF7Pr6I9RwzESKlOdnqJSur/lZDDzLIFVQxmawvJRuIVz1jJZE8Lw97UloqL6X0bFE+vduRcsJk42B6WkV33BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TransactionDate = _t, #"  Product" = _t, Sales = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," : "," ",Replacer.ReplaceText,{"TransactionDate"}),
    tab = Table.TransformColumnTypes(#"Replaced Value", {{"Sales", Int64.Type},{"TransactionDate", type datetime}}, "en-US"),
  
  
    #"Grouped Rows" = Table.Group(tab, {"  Product"}, {{"towk", each List.Sum(Table.SelectRows(_, each Date.IsInCurrentWeek(_[TransactionDate]))[Sales])},{"prwk", each List.Sum(Table.SelectRows(_, each Date.IsInPreviousNWeeks(_[TransactionDate],1))[Sales])},{"prday", each List.Sum(Table.SelectRows(_, each Date.IsInPreviousNDays(_[TransactionDate],1))[Sales])},{"today", each List.Sum(Table.SelectRows(_, each Date.IsInCurrentDay(_[TransactionDate]))[Sales])}})
in
    #"Grouped Rows"

[Fowmy]

@Anonymous 

Where do you need this the results as columns in Power Query, in Power BI as Columns or As Measures in Power BI. 
I suggest having it has Measures so you can slice and dice on the report.

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Anonymous]

@Fowmy 

Thanks for responding, i would like to have it as coulmns in power query since that is what i have been using but if i can also get it as a measure in power bi (both)  for educational purpose that would be AWESOME. Thanks 

[Fowmy · Accepted solution]

@Anonymous 

Here you go!

I added some additional data to test. Paste below code in a new blank query,
Check the completed PBI file: https://1drv.ms/u/s!AmoScH5srsIYgYF1HIrlsUkx7wkVrQ?e=Na2l88
Check the "Added Column" steps and group to learn how it was done.

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Anonymous]

@Fowmy  

Thanks for answering this question, I'm just curious if there is a better 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)

[Anonymous]

@Fowmy  

First of all, i want to say you are AWESOME and a God sent. You gave me more than i asked for, you read my mind that i actually needed the extra column you added so thanks for that. After running your code, i noticed that i was able to validate each of those column except for the "Previous Day". Despite that the original "Sold" column has values for that date field (ReadingDate), you code gave me zeros all through which means it didn't pick them like it should. I'm not sure why it's like that even though the code ran correctly. The snapshot below would give you an idea of what I'm talking about. How can i solve this?

[Fowmy]

@Anonymous 

Compare with the data I have in my file as I generated my own data. Go to the Source step and check the data.


If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Anonymous]

@Fowmy 

Thanks so much, i now know why it was giving that problem. It's because its a datetime field, it was corrected once i converted it to a date field. Thanks so so much for your help. If i need help I'm definitely reaching out to you 😂. Again thanks. 

[Anonymous]

Thanks for responding, i would like to have it as coulmns in power query since that is what i have been using but if i can also get it as a measure in power bi (both)  for educational purpose that would be AWESOME. Thanks