Investigate two groups/elements to predict a third

[Syndicate_Admin]

​Hello.

Until now I've always tried to reach my destination trying to do on my own, but this goes beyond my skills - way beyond...

To simplify, we have three shops (A02, L02, L07) that need cash to work, and the problem since the very first day, was to guess the fair amount of cash needed (you don't want to stay without cash, but you also need to avoid to have lot of cash in your pocket, especially if not needed). To cut a long story short, it seemed that we were very often wrong - certain days you needed up to twenty times the daily budget to buy but you had no cash, and certain days you had nothing to buy, but instead made lot of sells, ending with a huge cash. All three shops have different behavior - what could be a positive cash on Monday on shop 1, could be the opposite in shop 2, etc. I realized that there must be some specific local conditions that create this.

Manually I've tried to identify some trend, and I find it, and realized the query you can see in the screenshots, but I also see that I must be more precise, and now I think what I need to do is to group and compare the behavior of two day to guess the next day, that is, investigate on today, and yesterday, to predict tomorrow -let's call it 3 Days Pattern (3DP).

So far as I said, to have a total idea of the bias, I grouped by shop \ Type (sell or buy) \ DayOfWeek, and aggregated by NP(Sum Of Cash) \ Count, but this will miss many important shifts in local behavior, so I need to identify when these changes in the average pattern (showed by the screenshots) may happen, and I thought that the ratio could be, in example "Usually on Monday on shop 1 we expect to slightly buy, and very little sell, but if you see a change of the usual 3DP".

But I really don't know how to do that, I have several levels of issues in conceptualize the task, i.e.:

1) It looks like I need to something like involve two rows above - easy in Excel, but very slow in Power Query ( using the { [Index] -1 } method, in example) - how to do it?

2) should I break the project in Five different queries, based on the five combinations of 3DP? that is, a query for Thu-Fri to guess Mon, a query Fri-Mon to guess Tue, etc

Sorry for a very long message, and thank you

[v-angzheng-msft]

Hi, @Syndicate_Admin 

Could you please consdier sharing a simple sample file without any sesentive information for further discussion?

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Usually seasonality plays out week over week (for example monday vs monday), as well as year over year (including holiday season patterns).    Three day patterns are not something I have heard before. I can't judge if they are applicable to your industry.

I'd go with a 7 day pattern for starters.

[Syndicate_Admin]

Patterns are not the subject of investigation - they are consistent, and you could see them in the second screen shot. What I'm trying to do is now to identify how the exceptions to the average patterns happen, and my current analysis involves the three days analysis.

Thanks