Strange behaviour with cumulative sum of fraction

Anything related to PowerPivot and DAX Formuale
Posts: 59
Joined: Wed Sep 23, 2015 9:56 pm

Strange behaviour with cumulative sum of fraction

Postby andredl » Fri Jun 29, 2018 5:51 pm

My situation is this: I have budget by day for selling an item, some order history, and a physical stock on hand value on a particular day (the 27th of June in my example). Values relate to the start of the day, and I can have orders with future dates. My budget is only aligned to working week days, but it is possible that I can get orders on a weekend.

I want to project the future stock position over time if I take into account these existing sales orders and if I assume I will sell to budget (unless I oversell, in which case I need to go with orders taken).

On any given day, I can calculate what the total monthly budget is (40 in the example given), and I can sum up the month-to-date orders, giving me an idea of how I'm tracking for each day. The difference between these two measures tells me how many orders I am still short for the month, overall (4, on the 27th of June, in the example). This number is not allowed to be negative, of course, so when orders exceed budget, I don't expect that I will sell any more product in that month, even though in reality, it may still happen.

Of course, I can also determine from looking at my original budget by day, that I might expect to sell 2 units each day for the 27th, 28th and 29th of June (6 units), but doing so would violate my total monthly expectation (40), because I have already sold 36 for the month on the 27th.

I don't want to forecast that I will exceed my predicted budget for the whole month, unless of course the combined firm sales orders for the month exceed the total budget for the month, so in the example I want to spread out the remaining 4 units I expect to sell over the remaining 3 working days. Fractions are OK. To calculate this number by day, I do a ratio calculation where I divide the original budget for the remaining days (2 per day, for the 27th, 28th and 29th) by the total remaining units for the month as determined by the original daily budget (6 units), and multiply that number by the overall amount I'm still short on for the month (4 units), resulting in 1.333 units per day ("Remaining orders expected by day"). Over the 3 days this adds up to 4 units, which is what I expected.

I then calculate a balance equation, that accounts for my existing stock and this new remaining order expectation by day, and up to this point, everything looks fine.

Next, I do a cumulative calculation on that measure over time (Projected stock by day), and this is where the proverbial wheels come off. The first value I'm expecting to see on the 27th of June, is 116.67. The value for the 28th should be 115.33, the 29th should be 114, and the value on the 1st of July should also be 114, because I haven't sold anything on the 30th of June or the 1st of July, nor do I expect to. The result of the Projected stock by day measure makes no sense to me (hence the strange behaviour accusation), and I am at a loss for how to obtain my desired result in this measure.

I have obviously not tried everything, but I have tried many different things with no success. I think the issue arises from the "Remaining orders expected by day" calculation, because if I just use "Original Budget by Day Remaining @Date" in its place inside the Balance equation, the "Projected stock by day" measure behaves fine.

Does anyone have any illuminating ideas?

Stock projection.xlsx
(651.84 KiB) Downloaded 63 times

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests