Compare future and past trends in same table

I currently have two separate tables in my report that I am struggling to reflect in the same table due to date issues. One shows me my future forecast trend as a % compared to the same period last year. The time period is selected using a slicer. The calculation is:

Fcst vs Sales LY % = DIVIDE([S&OP TY],[Sales LY],0)

My other table is shows me the sales trend for the most recent 3 month period on the same item. The formula is:

3 month YoY % Change = [Rolling 3 Month Sales trend (%).]-1

The rolling three month sales trend formula is:

Rolling 3 month Sales Qty (units) = CALCULATE([Sales Qty (units)],DATESBETWEEN(Dates[Date],MAX(Dates[Date])-90,MAX(Dates[Date])))

Last Years rolling three months is:

Rolling 3 month Sales Qty (units) LY = CALCULATE([Rolling 3 month Sales Qty (units)],DATEADD(Dates[Date],-1,YEAR))

Now if I try to reflect my most recent sales trend in the same table as my forecast vs sales LY % it doesn’t work - presumably because the MAX date part of my rolling forecast is looking into the future as opposed to today.

How can I best adapt my formulas so the rolling 3 month trend is based off today?

Where I am trying to get to is a table that shows my current three month trend and the trend I am forecasting in the future compared to sales for the same period last year. This way I can see anomalies where I am forecasting a 10% increase year on year even though the current trend is showing a decline for instance.

Can you place in image of that table, that would help a lot.

Also the model as well.

Thanks

Sure - this first image shows the pickle I have gotten into. The top table shows the trend percentage of my forecast vs sales last year. It is attached to the date slicer at the top which allows me to choose the forecast period I want to compare and it will look at sales for the same period last year.

The second table is where I need to get smarter. It shows my rolling 3, 6 and 12 month trend but uses the month I am in as the anchor. To do this I have to use a filer for current year and select my current month from a slicer. This is why it falls apart if I bring them into the same table. The max date formula does not work because my date fact table has dates in the future to allow for forecasts. It would perhaps work if I could reference the max date in the ‘sales’ fact table because this would always have the latest month as the max in it but it would have to ignore the forecast date selection slicer somehow? I need some way of anchoring in the latest month whilst still allowing the rest of the table to use the forecast date slicer.

This is my model

I’ve read this about 7 times and still not quite understanding what you need here. Can we break this down to just one calculation and build up from there.

There’s a lot going on in this one report page with filters up the top, slicer to the side and then a page filter. All of these may or may not be impacting the formula and it’s confusing me a bit.

Breaking things down simply and building up is always how I personally solve things even myself, so step by step I can see the impact certain things are doing to any results I receive from formulas.

I’m pretty sure this isn’t that difficult (time intelligence rarely is I find) I just not getting the scenario as there’s too much going on.

Chrs
Sam

Sure, I think the problem lies in the fact that I haven’t been sophisticated enough with my formulas and reliance on date slicers has caused the problem.

So, the top table that gives me the fcst vs sales last year % is this formula:

Fcst vs Sales LY % = DIVIDE([S&OP TY],[Sales LY],0). This is fine I believe.

The problem is the bottom table with the trends. The formula relies upon a previous formula to calculate rolling 3 month sales:

Rolling 3 month Sales Qty (units) = CALCULATE([Sales Qty (units)],DATESBETWEEN(Dates[Date],MAX(Dates[Date])-90,MAX(Dates[Date])))

I had to use a filter on this to tell it the latest month as my date table has dates going into the future so MAX is no use to me. This filter acts as my anchor to say ‘The month is now April 2018 so my three month trend should be based on Feb, March and April 2018’. I now want to see that trend % in the top table. Does this make any sense?

I wouldn’t overthink it. With time intelligence you rarely have to be to sophisticated in your formulas. It’s should be relatively straight forward if everything is setup intuitively.

One thing I wouldn’t do is place to date time slicers on a page that work differently. This I believe it a recipe for big confusion for the developer and eventually the consumer.

I also feel like you may need to add a ( -1 ) to your top formula, to get the true % change.

I really think how this is setup needs to change.

Your MAX date part of the formula is going to be impacted for the top slicer, and then your selected April which isn’t even in that time frame.

How come you’re analyzing April rolling sales then wanting to place this into a table which select December time frames? Are you just wanting to compare a time period versus another randomly selected time period.

The other issue I’m still not quite getting is you have a monthly context down in the bottom table, but then you’re saying you want to place this into a daily context in the top table (which is being filter by another slicer). This theoretically isn’t possible.

Also have you had time to go through this course.

I think some confusion may be coming from the model setup. I can see some good setup there, but still looks like there is many optimizations there that would make all much more intuitive.

Chrs
Sam

Hi Sam,

I think you are right - I am going back to the drawing board on this one and will work through the module above over the next few days. Hopefully I will answer my own questions in doing this and have a better model as a result - if not, once I have tidied everything I might be able to at least ask a clearer more simplified question. Thanks fir your help.

Ok sounds good. Will be interested to see how to model looks after a few refinements. Chrs