Data in Column Changes each Period - Forecasting - Infor LX Database - function needed for Power BI

[jcrodriguez]

Hello All,

I am fairly new to the forum. Forgive me if this has been covered; however, I do not know how best to describe my problem without providing sufficient details. 

Background:

At my organization we Infor ERP LX, this program offers a forecasting module that allows me to generate new forecast at month end using the information saved in the Transaction History File (ITH). The information from the ITH file is pulled into the forecasting module when the forecast is generated/model and is saved in a file called JFI. Once reviewed and adjusted it can be uploaded into MRP/MPS via a file called KMR.

I am tasked with creating a forecast accuracy report in power BI to take a look at how the forecast performed against the actual on a monthly basis as part of the analysis prior to uploading into the MRP.

The Issues:

F1F11 (Year 1 Forecast, Forecast Period 1)

F1H112 (Year 1 Forecast History, History Period 12)

When I extrapolate the JFI file the Periods are listed as follows: F1F11, F1F12, F1F13...F1F113 (this represents 13 period buckets - we operate on a 12 period bucket). Each month the current month drops from what would be considered F1F11 (first position) into the forecast history file (F1H112 (last position)) with the subsequent month (previously F1F12) taking its place. 

The Goal:

I want to be able to convert Forecast JFI column headers to be recognized as the current month at month change. In theory using March as my starting month it would look like this:

• Forecast History: February, 2018 (F1H112)
• Current date: March 28, 2018 (F1F11)
• April, 2018 (F1F12)
• May, 2018 (F1F13)
• etc...

When I do my Forecast Generation for March 2018 (April 1st)

• Forecast History: March 2018 (F1H112)
• Current Date: April 1, 2018 (F1F11)
• May, 2018 (F1F12)
• June, 2018 (F1F13)
• etc...

When I do my Forecast Generation for April 2018 (May 1st)

• Forecast History: March 2018 (F1H111)
• Forecast History: April, 2018 (F1H112)
• Current Date: May 1, 2018 (F1F11)
• June, 2018 (F1F12)
• July, 2018 (F1F13)
• etc...

The Purpose:

To be able to pair it up with the Transaction History (actual) for the proper measurements to take place when I use a date filter.

Such as:

Date Slicer - Dates setup as Monthly Bins - Select March 2018

Results =

• Transaction File: March 2018: Data
• Forecast File: March 2018: Data (regardless of position within the Forecast Period; F1F11, F1H112)

This is also so that when I create a calculated measures between specific months or month groups.

---

As mentioned before, I have no idea what its called specifically when the data moves from one column to the next with regards to the way our system does it; therein I do not know how to investigate this further - this is why I am asking for guidance on where to search or how to do this.

Thank you in advance. 

Kind regards,

Jose

[jcrodriguez]

Hi All,

I was hoping to see if anyone has come across a similar scenario. I am still trying to figure out how to handle data that rolls over each month. when trying to compare agaisnt static information. 

Thanks.

[v-yulgu-msft]

Hi @jcrodriguez,

It's hard for me to imagine your scenarion without any data. And your actual environment looks like complex. Would you please provide some dummy data? Also, please make your requirement more specific rather than tell us a general request for a report. You could follow this blog to provide some information. 

Best regards,

Yuliana Gu

[jcrodriguez]

Hi @v-yulgu-msft

Thank you for your guidance. 

JFI looks like this when exported into Power BI:

so the top table is the JFI and the bottom table is called the KMR. In the system the KMR is static while the JFI is dynamic due to the fact that when I run my forecast at month end.

basically -> month end is ran -> March Forecast Data moves to last position in the forecast history group (F1H112) -> April Forecast moves to current forecast (F1F11) position. The process will repeat at month end when April Forecast moves to forecast history and June forecast replaces it in the forecast position (F1F11).

I need a way to be able to calculate a stastic number against a dynamic number. basically if I wanted to find the difference between March Forecast in JFI against the March forecast in the KMR, even when March Forecast in JFI moves at the month end when the new forecast pushes the old data into a different column.

Hope this helps explain it out further.

Thanks!

Kind regards,

Jose