Calculate previous forecast snapshot data - different time periods

[madkow]

Hi All,

I have a table that will be populated weekly, though it could be shorter or longer, with a snapshot of the forecast at that time period.

Basically, this report will show a trend over time, if the forecast went up or down.

This a very simple example of the data.

In the real data, there are plenty of other data points and forecasts can be in different statuses (hard/soft) but this should show kind of what the data looks like.

What I want to do is capture the previous entries forecast.  An example would be the East product B 1/1/18 versus 1/7/18 forecast.

Once I can capture the previous entries forecast I can then do differences and all kinds of fun stuff.

I can successfully get the previous date.  If it's 1/7 I know that 1/1 is the previous entry.  But I cannot figure out how to use that so I can determine the previous periods total.

I've tried things like earlier and nonblank but was not getting the results I wanted.   Since this is not a daily report of data and can be all over the place, I wanted to figure out an elegant solution to this.  

Thanks in Advance!

[Srikanth9000]

Last 26 week's forced hours power bi dax please 

[Seward12533]

Lots of ways to tackle this can you give us a better idea of what your data, tables, relationships and measures look like.

But if you had divinnected slicers to choose base forecast and comparison forecast. And you have a [measure] you want to calculate say
Selected Base = SELECTEDVALUE(BaseSlicer[Date],MAX(BaseSlicer[Date])) // harvest selection default to latest
Forecast Value = AVERAGE(Table[Forecast]]
FCST Base = CALCULATE([Forecast Value],Table[Date]=[Selected Base FCST])

Tip you can dynamically greater the disconnected slicer tables as a calculated table using DAX
Base Slicer = VALUES(table[Date])

[madkow]

Thanks for the reply!

The top portion would be a sample of what the data would look like.  There are a couple more data points per row but this is somewhat simplified.  The production date could have thousands of records per date across dozens of regions with hundreds of products split amongst varying forecast statuses.

The bottom would be a matrix visualization of what I'm after.  If the forecast value increased (green) or decrease(red).

My measures for this example would be:

Hard Forecast = CALCULATE(SUM('Forecasts'[Forecast Amount]),'Forecasts'['Forecast Status'] = 'Hard')

And so on for each forecast status.

I can determine what the previous date is. I store this as a calculated column

Pre_Date = CALCULATE(MAX(Forecasts[Date]),FILTER(Forecasts,Forecasts[Date] < EARLIER(Forecasts[Date])))

If I'm on a Date of 1/7 the Pre_Date is 1/1.   That works great.

The sticking point is if I am on a record for 1/7, what's the corresponding forecast for 1/1?

A measure would be great.  That way any slicers I have on the report can slice the data accordingly.

I tried something like this in a calculated column but that didn't work.  Returns nothing

HF Previous = CALCULATE([Hard Forecast],Forecasts[Date]=EARLIER(Forecasts[Pre_Date]))

Tried it in a measure but "EARLIER refers to an earlier row context which doesn't exist'.

If I had a full calendar forecast daily I could use time intelligence like previousday.

But this will have gaps and they are not consistent.  I looked into nonblank functions but they were not really working for me or I was not understanding correctly.

Thanks again for your help.

[Ashish_Mathur]

Hi,

I believe this is the result you want.  You may download my PBI file from here.

Hope this helps.

[madkow]

Hi Ashish,

Not quite.  I already have the matrix.  I need a way to compare previous forecasts dates.

For instance what was the difference between the Hard Forecast for 1/1 as compared to 1/7.

Thanks

[Seward12533]

My approach would be very different.  I honestly dont use earlier much but I have a build a very simlar report to what your trying to do with hunderds of thousands of records.  No calculated columns added to the source data or summary tables.

If you had a sample with some representative data woudl be easier ot show you. 

Assuming you have a linked date table the measures woudl be

Latest Forecast = MAX(Forecast[Date])
Previous Forecast = CALCULATE(MAX(Forecast[Pre_Date]),Forecast[Date]=[Latest Forecast]
Hard Forecast Latest = CALCULATE(SUM('Forecasts'[Forecast Amount]),'Forecasts'['Forecast Status'] = 'Hard',Forcast[Date]=[Latest Forecast])
Hard Forecast Previous  = CALCULATE(SUM('Forecasts'[Forecast Amount]),'Forecasts'['Forecast Status'] = 'Hard',Forecast[Date]=[Previous Forecast])

You then build a matrix with Region, Product or whatever on the rows and nothing on the columsn and then put the measures in the values.  They wont display the Date reference in the  table since they will say Lastest  and Previous so I would add a MultiCard visual to show what the Lastest and Previous Forecasts where.

I think if you did this and put forecast date in the column and only showed the Latest Forecasts but use a DELTA forecast measure the Just took teh Latest - Previous to selectively format the cells it woudl show a matrix of all yoru forecast dates which would you slice with fliters. the first one where the previous period forecast is filtlered out might get funky and the previous forecast would need to be updated to use all dates IF you had a calendar table and were filtering the dates there.

Another approch woudl be to use the disconnected slicers to allow the user to choose the Base Forecast (instead of latest) and the Previous Forecast for comparison with defaults to the latest and the one before it based on the measured above if nothing is selected in the slicers.

[madkow]

Thanks but that's not quite what I am after.  I need to say all the dates.  Not just in a matrix but for any visualization.

In the end, I want the user to be able to compare week over week changes.  The only slicer will be what time frame do you want to compare.

If I can create a measure that automatically gets the value from the previous date then that's all i need to create other metrics for this report.

[madkow · Accepted solution]

Thanks for assisting me.  With your help and little more digging, I found a solution.

First thing is you need a calendar date table.  Didn't think I would need one but now I know better now. 

The table is Forecast.  History Date is the date the data was captured

Pre_Date is a column in the Forecast table.  This gets the previous date with a value.

Pre_Date = CALCULATE(MAX(Forecast[History Date]),FILTER(Forecast,Forecast[History Date] < EARLIER(Forecast[History Date])))

The Hard Forecast is a measure that totals the hard forecast from Forecast table.  It uses the Pre_Date

HF Previous = CALCULATE([Hard Forecast],FILTER(ALL(Forecast),Forecast[History Date]=MAX(Forecast[S Pre_Date])))

Once I had that I was able to determine what the last value was for the previous date entered.  The dates in Forecast were not consecutive, you could have a 3, 5 or 7 gap between dates.  From there I could determine calculations such as differences from one history date to the next.

Thanks Again!