Running totals for all calendar days (and grouped fields)

[Sibrulotte]

Hi, 

I have a set of data that are transactions, per product (Apples and pears) per type of transactions (Buy and sell) and a date of transaction. 

I found this amazing video to do a running total in power query that was pretty easy to follow in the end. Really recommnend it:

https://www.youtube.com/watch?v=MCkLi49cc8Y

BUT! 

The result only works for transaction lines that have all products and all types of transactions. I can't get a balance on products if it  hasn't been sold that specific day for exemple. So I need to fill all days with all products and all types of transactions with 0$ to get a full transaction table. 

Anyone has a recommendation for that?

[Jimmy801]

Hello @Sibrulotte 

could you please share the data example and your M-code to get a better understanding of the problem you have.

Please share also the expected outcome

All the best

Jimmy

[Sibrulotte]

Sensitive information on my data set so I'll make some up:

Transaction table:

Date,Product,TransType,Amount

2020-01-01,Apple,Buy,50

2020-01-01,Pear,Buy,100

2020-01-02,Apple,Sell,25

2020-01-04,Pear,Buy,60

After:

Date,Product,TransType,Amount

2020-01-01,Apple,Buy,50

2020-01-01,Apple,Sell,0

2020-01-01,Pear,Buy,100

2020-01-01,Pear,Sell,0

2020-01-02,Apple,Buy,0

2020-01-02,Apple,Sell,25

2020-01-02,Pear,Buy,0

2020-01-02,Pear,Sell,0

2020-01-03,Apple,Buy,0

2020-01-03,Apple,Sell,0

2020-01-03,Pear,Buy,0

2020-01-03,Pear,Sell,0

2020-01-04,Apple,Buy,0

2020-01-04,Apple,Sell,0

2020-01-04,Pear,Buy,60

2020-01-04,Pear,Sell,0

With that, I'll take the code I got from the youtube video to Get a balance each day for all my products per type of transaction.

Date,Product,TransType,Amount,Balance

2020-01-01,Apple,Buy,50,50

2020-01-01,Apple,Sell,0,0

2020-01-01,Pear,Buy,100,100

2020-01-01,Pear,Sell,0,0

2020-01-02,Apple,Buy,0,50

2020-01-02,Apple,Sell,25,25

2020-01-02,Pear,Buy,0,100

2020-01-02,Pear,Sell,0,0

2020-01-03,Apple,Buy,0,50

2020-01-03,Apple,Sell,0,25

2020-01-03,Pear,Buy,0,100

2020-01-03,Pear,Sell,0,0

2020-01-04,Apple,Buy,0,50

2020-01-04,Apple,Sell,0,25

2020-01-04,Pear,Buy,60,160

2020-01-04,Pear,Sell,0,0

And that's pretty much it.

[Jimmy801]

Hello @Sibrulotte 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Sibrulotte]

Hi, 

so thank you very much for the effort put in this. I was not able to integrate the code you had provided. But I did find another way to achieve my goal quicker.

I created a table in my excel workbook with all possible combinations of fruits and transaction type.

Fruit;Type

Apple;Buy

Apple;Sell

Orange;Buy

Orange;Sell

I then added a custom column with two parametres that I retrieved through list queries.

List.Dates(Date_premier_fichier,Duration.Days(Date_dernier_fichier-Date_premier_fichier)+1,#duration(1,0,0,0))

Date_premier_fichier and Date_dernier_fichier are parametres that have been retrieved by doing a List query and using that to define the parameter:

vDatesDernier was obtained by running a query that had only the maxdate of all files and converting that into a list. (transform --> convert to list)

The result was a table with all possibilities of fruit, transaction types, and dates.

Success 🙂

[MSAInitaite]

Hi 

If you create one more dataset, where you have the distinct values of the lines you want e.g.: 

Pear, Sell,0

Pear, Buy,0

Apple, Sell,0

Apple, Buy,0 

Then you create a column with a list of dates (from the first date you need until the last you need, maybee todays date and Expand that. Then you have a list with all buy/sell for your products. merge (left join) your current list onto the one just created (on date, product and TransType)

You can now have to fix the amount column, so that it is the number that you actually have or else just zero. 

Hope it helps

[Jimmy801]

Hello @Sibrulotte 

I'm not watching any youtube video to try do understand somebody elses problem 🙂 sorry

but when I'm looking at your data, what you basically try to achieve is based on a transaction list, create a daily list including the current ballance of that product. Is that right?

If yes, i don't get logic for example this 3 lines

2020-01-02,Pear,Sell,0,0 => in the line before the ballance was 100

2020-01-03,Apple,Buy,0,50 

2020-01-03,Apple,Sell,0,25 => how can that be when on the row above you had 50, why now is 25?

Jimmy

[Sibrulotte]

yeap, 

so, 

2020-01-02,Pear,Sell,0,0 => in the line before the ballance was 100  Not true, since there has not been any Sell transactions on Pears before. 

2020-01-03,Apple,Buy,0,50 

2020-01-03,Apple,Sell,0,25 => how can that be when on the row above you had 50, why now is 25? The only sell transaction on apples before that was for 25. 

I think my expemple might have been easier to understand if we changed transaction type to say, region. So replace all "Buy" with "North America", and all "Sell" with "Europe" if you need a better grasp on my end result.

[Jimmy801 · Accepted solution]

Hello @Sibrulotte 

i had some deep thoughts.. quite tricky to achieve. But I got a solution for you. Importan is that your starting source has for every row you need an example. So to get it work I had to add a New row with Pear and Sell with Amount null

let
	Source = #table
	(
		{"Date","Product","TransType","Amount"},
		{
			{"43831","Apple","Buy","50"},	{"43831","Pear","Buy","100"},	{"43832","Apple","Sell","25"},	{"43834","Pear","Buy","60"}, {"43834","Pear","Sell",null}
		}
	),
    ToDate = Table.TransformColumns
    (
        Source,
        {
            {
                "Date",
                each Date.From(Number.From(_))
            }
        }
    ),
    ChangedType = Table.TransformColumnTypes(ToDate,{{"Amount", Int64.Type}}),
    AddedDateMin = Table.AddColumn(ChangedType, "DateMin", each List.Min(ToDate[Date])),
    AddedDateMax = Table.AddColumn(AddedDateMin, "DateMax", each List.Max(ToDate[Date])),
    Group = Table.Group(AddedDateMax, {"Product", "TransType"}, {{"AllRows", each _, type table [Date=date, Product=text, TransType=text, Amount=number]}}),
    Transform = Table.TransformColumns
    (
        Group,
        {
            {
                "AllRows",
                (tableint)=>
                let 
                    ListOfDatesToAdd = List.Difference(List.Dates(List.Min(tableint[DateMin]), Duration.TotalDays(List.Max(tableint[DateMax])-List.Min(tableint[DateMin]))+1,#duration(1,0,0,0)),tableint[Date]),
                    CreateRecords = List.Transform(ListOfDatesToAdd, (dateint)=> Record.AddField(Table.First(Table.RemoveColumns(tableint,{"Date","Amount", "DateMin","DateMax"})),"Date",dateint)),
                    Combine = Table.Combine({Table.FromRecords(CreateRecords), Table.RemoveColumns(tableint, {"DateMin", "DateMax"})})
                in
                    Combine
            }       
        }
    ),
    RemoveOther = Table.SelectColumns(Transform,{"AllRows"}),
    Expand = Table.ExpandTableColumn(RemoveOther, "AllRows", {"Product", "TransType", "Date", "Amount"}, {"Product", "TransType", "Date", "Amount"}),
    ChangedType2 = Table.TransformColumnTypes(Expand,{{"Date", type date}, {"Amount", Int64.Type}}),
    Sort = Table.Sort(ChangedType2,{{"Date", Order.Ascending},{"Product", Order.Ascending}, {"TransType", Order.Ascending}})
in
    Sort

Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[KNP]

Hi @Sibrulotte,

If your dataset product column always has all of the possible products (just not on every day) you could pivot the Product column, replace nulls with 0 and then unpivot.

If I'm understanding you correctly I think this will give you the output you require for the next step.

(Thanks for the video link by the way, I haven't seen running totals done that simply before)

Regards,

Kim

[Sibrulotte]

Hi, totally understand you on that you tube video. I puyt it up because it was actually super useful to me and hopefully to someone reading this later on that would need help.

So I'm NOT trying to get a balance of apples and pears (buy adds, sell substarcts).

I'm trying to get a balance of how many apples we're sold, how many apples we're bought, how many pears we're sold and how many pears we're bought.

[Jimmy801]

Hello @Sibrulotte 

okay, but nevertheless. Could you explain then the how the last number of my 3 lines posted are calculated?

Jimmy